CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008validation
raupach
raupach

Reputation: 3102

How to rewrite phone numbers in T-SQL?

I have a beginners question in T-SQL.

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 a CSV file, 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 T-SQL.

Any ideas?

Upvotes: 3

Views: 3147

Answers (3)

dance2die
dance2die

Reputation: 36985

"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

Upvotes: 0

JFV
JFV

Reputation: 1783

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

Upvotes: 2

Pawel Krakowiak
Pawel Krakowiak

Reputation: 10100

Something like

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

should work. Doesn't look pretty. ;)

Upvotes: 4

Related Questions