views:

507

answers:

4

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?

A: 

Here is a good article pertaining to SQL Server.

notandy
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.
A: 

"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
Sung Meister
What is #t? Never seen that one before.
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
Sung Meister
+1  A: 

Something like

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

should work. Doesn't look pretty. ;)

Pawel Krakowiak
That was easy :)
+1: Ha, i overthunk. This looks better than what I suggested. ;)
Sung Meister
@nooomi: You said you like Perl... :P
Pawel Krakowiak
+1  A: 

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
JFV