SQL Server Performance Forum – Threads Archive

Cleansing Data

Hi, I am trying to cleanse the data in a table…particularly the phone numbers. I would like to delete any (), -, kind of stuff. Can any one suggest me a best way of doing it. Thanks in advance. Madduri

hello madduri, i have just finished a large conversion where i had to clean up phone numbers among other things. What i fould usefull was the stuff() command and replace() command do a google search for "tsql stuff" etc and you will get the exact code to do it. the main commands i used the most for phone number cleansing was left, right, nested left/right, replace(), stuff(), isnumeric = but remember to surround your query with transaction code because i learned the hard way <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> begin tran update code if @@rowcount > "expected row return" rollback tran else committ tran my syntax may be slightly off <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Hi Phthisis, Thanks for your reply. Basically I am a DBA and do not know much of developing. Do we have to update each and every record manually?
I am looking for a querry which can search for those characters in the table and delete them. I hope this is possible. Thanks, Madduri

first you need to identify all the special characters that your data could have. Then you can do a batch update in one query.
Update yourTable
SET PhoneNumber = REPLACE(REPLACE(REPLACE(PhoneNumner, ‘-‘, ”),’)’,”),'(‘,”)
the above code will replace all ‘-‘ and ‘(‘ and ‘)’s. If you have more special characters you need to add more REPLACe’s accordingly. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

Thanks ndinakar..It worked!! Can we update all the columns at a time or we can work with one column at a time. Thanks, Madduri

quote:Originally posted by ndinakar first you need to identify all the special characters that your data could have. Then you can do a batch update in one query.
Update yourTable
SET PhoneNumber = REPLACE(REPLACE(REPLACE(PhoneNumner, ‘-‘, ”),’)’,”),'(‘,”)
the above code will replace all ‘-‘ and ‘(‘ and ‘)’s. If you have more special characters you need to add more REPLACe’s accordingly. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

This one is smart! Thanks, Name
———
Dilli Grg (1 row(s) affected)

Unless you specify a WHERE condition ALL rows will be updated. If you wan tonly few rows, use an appropriate WHERE clause. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

]]>