Reformat Phone Number

Question

Reformat Phone Number

DBayliss

Mr or Mrs. 500

Points: 530
More actions
September 11, 2012 at 8:18 am

#261717

Hi,
I am trying to create a process to reformat telephone numbers in a table. Below are the main types of errors I'm faced with e.g. letters at the end of the numbers, spaces in middle, mobile numbers missing a leading zero. Would anyone be able to provide some information about the best way to resolve/program the below issue.
All help would be much appreciated.
Examples of what is in the database and what I would like the output to be.
01473 743719 Ray ==> 01474743719
01226386486 – clt ==> 01226386486
07763 650906 ==> 07763650906
7894728202 ==> 07894728202

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 1

REPLACE() would take care of your spaces and hashes. You could also use the LEN() function to compare the data to what length the number should be in order to add your leading zero.

Caveat! If the number is being imported as a number, integar, etc, leading zeros will NOT stick. You have to make sure the phone number is imported or at least coverted, to a string.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

The best and easiest would be creating CLR function which will remove all non-digits from string using RegEx.Replace.

Also, you can create UDF in T-SQL (you will use PATINDEX and REPLACE there), but it will not perform as fast as CLR for large datasets.

However, looks like it's one-off task for you, so you can created UDF and see if its performance is satisfactory for your database.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 3

Eugene Elutin (9/11/2012)
The best and easiest would be creating CLR function which will remove all non-digits from string using RegEx.Replace.

Forgive me a moment, Eugene, while I poo-poo your "easiest" comment. Easy is only easy if you actually know how to code a CLR. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Steve Jones - SSC Editor SSC Guru Points: 738643 More actions · Answer 4

I'd lean on the CLR as well. It is tough, but there are good examples around: http://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Brandie Tarvin (9/11/2012)
Eugene Elutin (9/11/2012)
The best and easiest would be creating CLR function which will remove all non-digits from string using RegEx.Replace.
Forgive me a moment, Eugene, while I poo-poo your "easiest" comment. Easy is only easy if you actually know how to code a CLR. @=)

CLR is easier for me, you right. But it is the same for SQL one, you need to know how to code it in T-SQL too... 😉

Here is a T-SQL version for you:

CREATE FUNCTION dbo.f_RemoveNoneDigits ( @value VARCHAR(100) )

RETURNS VARCHAR(100) WITH SCHEMABINDING

AS

BEGIN

DECLARE @pi INT

WHILE 1=1

BEGIN

SET @pi = PATINDEX('%[^0-9]%',@value)

IF @pi = 0 BREAK

SET @value = LEFT(@value,@pi-1) + SUBSTRING(@value,@pi+1,100)

END

RETURN @value

END

GO

select dbo.f_RemoveNoneDigits ('01473 743719 Ray')

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Brandie Tarvin (9/11/2012)
Eugene Elutin (9/11/2012)
The best and easiest would be creating CLR function which will remove all non-digits from string using RegEx.Replace.
Forgive me a moment, Eugene, while I poo-poo your "easiest" comment. Easy is only easy if you actually know how to code a CLR. @=)

...and you are permitted to do so 😉

You can do it in TSQL if you need to, something like this:

SELECT

d.PhoneNumber,

l1.PhoneNumber,

l2.PhoneNumber,

l3.PhoneNumber

FROM (

SELECT PhoneNumber = '01473 743719 Ray' UNION ALL

SELECT '01226386486 – clt' UNION ALL

SELECT '07763 650906' UNION ALL

SELECT '7894728202'

) d

CROSS APPLY (SELECT REPLACE(PhoneNumber,' ','')) l1 (PhoneNumber)

CROSS APPLY (SELECT CASE WHEN LEFT(l1.PhoneNumber,1) <> '0'

THEN '0' ELSE '' END + l1.PhoneNumber) l2 (PhoneNumber)

CROSS APPLY (SELECT LEFT(l2.PhoneNumber,11)) l3 (PhoneNumber)

When I've had to do this in the past, the hardest part has been identifying numbers which need to be edited. This should get you started:

SELECT

PhoneNumber

FROM (

SELECT PhoneNumber = '01473 743719 Ray' UNION ALL

SELECT '01226386486 – clt' UNION ALL

SELECT '07763 650906' UNION ALL

SELECT '7894728202'

) d

WHERE REPLACE(PhoneNumber,' ','') NOT LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden