CODEWITHSUNDEEP
sqlselectsubstring
CO-VROD
CO-VROD

Reputation: 47

SQL phone number data correcting xxx-xxx-xxxx

I have a sql query to extract phone number from a Blackberry Enterprise Server to an asset type of sql database. That problem I am running into is that the BES does not standardize the phone numbers, sometimes there is the log distance dial code of 1, very random. But in the asset database I need the standard xxx-xxx-xxxx format. Here is my select statement for that phone number line. This limits the Phone number to the 10 characters, but I also need to push a dash as xxx-xxx-xxxx

SELECT RIGHT([PhoneNumber], 10) AS PhoneNumber.

Upvotes: 0

Views: 8238

Answers (1)

Hart CO
Hart CO

Reputation: 34774

If the dashes are consistent in the source then you could just take RIGHT(PhoneNumber,12):

If not, you should remove dashes, take the right 10, then add dashes back in:

SELECT SUBSTRING(RIGHT(REPLACE(PhoneNumber,'-',''),10),1,3)+'-'
      +SUBSTRING(RIGHT(REPLACE(PhoneNumber,'-',''),10),4,3)+'-'
      +SUBSTRING(RIGHT(REPLACE(PhoneNumber,'-',''),10),7,4)

Demo: SQL Fiddle

Syntax may vary by database.

Upvotes: 2

Related Questions