Go to Top

Cleaning phone numbers in MSSQL

Below are some code snippits that should help you with cleaning phone numbers in MSSQL. I recently used it to remove spaces and +44 from mobile phone numbers replacing it with a 0.

Before i start with the code i will explain a few symbols that i’m using here which people may or may not have seen before.

First of all %.
% is used in MSSQL to define any amount of characters of any particular length.
Second is _
_ is used in MSSQL to define one character exactly.

So now on with the code. The first two queries find any records which have a space in them and then remove the space from those records.
First check how many records have a space in it that start with +44 and are of type mobile

SELECT * FROM phone
WHERE (phonetype = 'MB')
AND (phonenumber LIKE '+44____ %')

The above query selects all records from the phone table where phone numbers start with +44 then four numbers then a space then any characters

Next to update phone numbers which start with +44 and have a space in it ** !NOTE! rows affected should match result of above query

UPDATE phone
SET phonenumber = REPLACE(SUBSTRING(phonenumber, 1, DATALENGTH(phonenumber)), ' ', '')
WHERE (phonetype = 'MB')
AND (phonenumber LIKE '+44____ %')

These next two queries will show and update any phone numbers that start with +44

/* check how many records have +44 on them and are of type mobile */
SELECT * FROM phone
WHERE (phonetype = 'MB')
AND (phonenumber LIKE '+44%')

The above query only selects records which start with +44 then any number of characters

Next we have to update phone numbers which start with +44 replacing with 0 ** !NOTE! rows affected should match above query

UPDATE phone
SET dbphonenumber = REPLACE(SUBSTRING(phonenumber, 1, DATALENGTH(phonenumber)), '+44', '0')
WHERE (phonetype = 'MB')
AND (phonenumber LIKE '+44%')

Hopefully these queries will be of use to other people.

If you have any questions feel free to leave a comment below.

Leave a Reply

Your email address will not be published. Required fields are marked *


4 + four =