Remove and Replace Special Characters in a SQL Server Column

Here’s a sample output of a SQL Server Column containing special characters, obtained from different sources.

As you can observe, the format of the numbers is not uniform. What is needed is a uniform format like 111-111-1111

Here’s how to remove and replace these special characters using REPLACE to obtain the desired output:

-- SAMPLE DATA
DECLARE @TT TABLE (Phone varchar(15))
INSERT INTO @TT VALUES
('(100)-111-2222'),
('(101)111-2222'),
('111-111-2222'),
('(110)-100-2222'),
('(111)111-2222'),
('112-111-2222'),
('(121)111-2222')

-- QUERY
SELECT
REPLACE(
REPLACE(
REPLACE(
Phone,
'(', '' ),
')-', '-' ),
')', '-' ) as Phone
FROM @TT

OUTPUT

Note: This approach works well when the number of characters to be replaced are few.

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: