How to rewrite phone numbers in T-SQL?

Question

I have a newbie question in T-SQL.

We imported Excel spreadsheets into SQL Server 2008. It is too bad that these Excel files were not formatted as they should be. We want the phone number to look like this: '012345678', with no spaces at the beginning and end, and no spaces inside. Even worse, sometimes a number is encoded with the prefix "0123-2349823" or "0123/2349823".

Usually I exported an Excel file to a CSV file , then ran the magic Perl script to clean up, and then reimport the Excel file.

However, it would be interesting to learn how to do such things with T-SQL.

Any ideas?

Answer 1

Sort of

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

must work. Doesn't look pretty .;)

Answer 2

I would do this 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

Answer 3

"Cleared" contains only a numeric value

Depending on whether the phone 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