How to replace specified string value with another string value in SQL select statement?

Question

I have table that has phone number in the format of 888.345.ABCD(2223). In my select statment I would like return 888.345.2223. I can do like following:

select replace (phone_number, 'ABCD(2223)', '2223')
from table1

But I could have different phone numbers, like 888.345.EDED(3333) or 888.345.EDED (3333). How do I write the query?

Update: But I could have phone number like 888.123.4567.

Answer 1

For both given cases:

declare @T table (phone_number varchar(50))
insert into @T values ('888.345.YYYY(2223)'),('888.345.XXXX (2223)')

Method 1: Using LEFT() and RIGHT() functions:

SELECT LEFT(phone_number,8) + LEFT(RIGHT(phone_number,5),4)
FROM @T

Method 2: Using STUFF(), SUBSTRING() and CHARINDEX() functions.

SELECT STUFF(phone_number,9,LEN(phone_number)-8,
             SUBSTRING(phone_number, CHARINDEX('(',phone_number)+1,4))
From @T

--Results
888.345.2223
888.345.2223

Answer 2

If your string is too complex, change of place, has different amount of characters or other funny behaviour then you should rely on regex expressions...but be sure you need it as it could happend to you as to this user that tried to warn others on using regex.

In case you think you could use it, check out how to create a good function to make some complex validations TSQL Regular Expression Workbench

Answer 3

You can try something like this

select STUFF(LEFT(phone_number, LEN(phone_number)-1),8,6,'.')
from table1

Internal LEFT removes the last ). Then STUFF replaces the rest of unneeded chars with .

Answer 4

Assuming that the format of the phone numbers doesn't change:

select replace (phone_number, RIGHT(phone_number, 10), SUBSTRING(phone_number, 14, 4))
from table1