Howto rewrite phone numbers in TSQL?

I have a beginners question in TSQL.

We imported Excel Sheets into a SQL Server 2008. Too bad these excel files were not formatted the way they should be. We want a phone number to look like this: '012345678', no leading and trailing whitespace and no whitespace within. Even worse sometimes the number is encoded with a prefix '0123-2349823' or '0123/2349823'.

Normally I would export the excel file to csv, then launch some magic perl script to do the cleaning and then reimport the excel file.

Still it would be interesting to know how to do stuff like this with TSQL.

Any ideas?

From stackoverflow

• Here is a good article pertaining to SQL Server.

  : Thanks, thats an interesting article but doesn't solve the issue. Guess that whole regex, substring thingy is way easier to do in perl than in tsql.

• "Cleaned" contains only numeric value

  Depending on whether a telephone number contains "-", "/", replace them with an empty string.

  create table #t ( tel varchar(30) )
  
  insert  #t select '0123-2349823' 
  insert  #t select '0123/2349823'
  
  select  tel,
       replace(tel, 
        case
         when patindex('%-%', tel) > 0 then '-'
         when patindex('%/%', tel) > 0 then '/'
        end, '') as Cleaned
  from    #t
  

  : What is #t? Never seen that one before.

  Sung Meister : It's a temporary table I used to test so that you can simply copy and paste the code above to see if it works in your environment

• Something like

  replace(replace(rtrim(ltrim('0123-2349823')), '-', ''), '/', '')
  

  should work. Doesn't look pretty. ;)

  : That was easy :)

  Sung Meister : +1: Ha, i overthunk. This looks better than what I suggested. ;)

  Pawel Krakowiak : @nooomi: You said you like Perl... :P

• I would go about it with an update and use the 'Replace' and LTrim/RTrim functions for SQL.

  Update Table1
  set phonenum = Case
       When phonenum like '%-%' Then LTrim(RTrim(Replace(phonenum, '-', '')))
        Else LTrim(RTrim(Replace(phonenum, '/', '')))
       End