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.
If number of characters to be removed is more in number, you can use either of these
ReplyDeletehttp://beyondrelational.com/blogs/madhivanan/archive/2010/01/08/replace-data-of-one-table-with-data-of-other-table.aspx
http://beyondrelational.com/blogs/madhivanan/archive/2009/05/11/removing-unwanted-characters.aspx