June 9, 2016 at 9:00 pm
hi,
I have a tableA in which PhoneNumber column have values like 911234567891.
i want to remove the first two digits from the start i.e 1234567891 and then i want to change it to phone number format xxx-xxx-xxxx so the ending result will be 123-456-7891.
I have already done it using seprate Select and Update query like this:
SELECT
[Phone Numbers]
,FORMAT([Phone Numbers],'###-###-####') AS [Formatted Phone]
FROM tbl_sample
UPDATE tbl_sample
SET [Phone Numbers] = SUBSTRING([Phone Numbers], 3, 8000)
WHERE [Phone Numbers] LIKE '91%'
Is it possible to do it using single select query only so that there will be no need to update the data first?
Any kind of help or suggestion will be appreciated.
June 9, 2016 at 10:22 pm
Example below using a CASE statement
Assuming phone numbers are entirely numeric:
;
WITH PhoneNumbers AS (
SELECT *
FROM (
VALUES ( 911234567891 ),( 1234567891 ),( 911234567891 ),( 1234567891 )
) PhoneNumbers(Number)
)
SELECT FORMAT( X.Number, '###-###-###' )
FROM PhoneNumbers P
CROSS APPLY ( SELECT CAST( RIGHT( P.Number, LEN(P.Number) - CASE WHEN P.Number LIKE '91%' THEN 2 ELSE 0 END ) as bigint ) Number ) AS X
June 10, 2016 at 1:15 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply