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.
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
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
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 .
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
