Phone # Normalization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Phone # Normalization

Author

Topic

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-13 : 09:18:27

Hi Folks,

I'm working on a new project at my new job that consist of matching phone #'s from a data feed to phone #'s in the database. The problem here is that there were no restrictions when users entered phone #'s so there's no consistancy. My question is...Is there a way to strip out spaces, parenths, and dashes from a column? If I can get a good result set I can just do a mass update. Any help would be greatly appreciated.

Thanks,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-13 : 09:27:33

You can use Replace() function...but be more specific. In what format is your current data and how you you want it to be? Post some sample data and expected output !

Harsh Athalye
India.
"Nothing is Impossible"

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-13 : 09:41:21

The field is currently a varchar(50) which is pretty nuts. some example
(510) 222-7366
514-844-5558
+45-77-30-90-00
+44 20 7330 6455
+44 (20) 7901-5050
+4420 7543 1579
+411 267 6717
+011-442-078-645747
+44207901 5918

What I'd like is to eliminate the +'s the dashes, the parenths etc.
I want nation #'s to look like 6174583696 and international numbers to look like
-442075431579 and I'd like to turn them into bigints.

Thanks again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-13 : 09:43:48

You need to use multiple replaces

Select Replace(Replace(Replace(col,'+',''),'-',''),'(','') yourtable

Madhivanan

Failing to plan is Planning to fail

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-13 : 09:53:03

Guys I appreciate the help...many thanks this is perfect.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-13 : 10:43:03

+011-442-078-645747

Good luck turning that into a bigint. Leading zero!

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-13 : 11:05:32

Yeah but its ok its for comparison purposes the leading 0 will be dropped on both sides. I'm having trouble converting them into bigint's though its saying that there was an error converting from varchar to bigint. I think the reason is because its keeping the spaces at the end of the #...this is a big mess. I'll keep trying a few things, but if you guys have any more suggestions please feel free :)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-13 : 11:14:08

Something like this?...

--data
declare @t table (col varchar(50))
insert @t
          select '(510) 222-7366'
union all select '514-844-5558'
union all select '+45-77-30-90-00'
union all select '+44 20 7330 6455'
union all select '+44 (20) 7901-5050'
union all select '+4420 7543 1579'
union all select '+411 267 6717'
union all select '+011-442-078-645747'
union all select '+44207901 5918'

--calculation
Select case when left(col, 1) = '+' then -1 else 1 end *
     cast(Replace(Replace(Replace(Replace(Replace(col,'+',''),'-',''),'(',''), ')', ''), ' ', '') as bigint)
from @t

/*results
-------------------- 
5102227366
5148445558
-4577309000
-442073306455
-442079015050
-442075431579
-4112676717
-11442078645747
-442079015918
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-07-13 : 11:19:04

Since it is a free-text field, I think you are better off filtering for specific (numeric) characters rather than using replace for all possible dissallowed characters:

CREATE function dbo.StripPhone(@PhoneNumber varchar(50))
returns varchar(50)
as
-- StripPhone
-- blindman, 3/04
-- Strips a phone number of non-numeric characters and leading 1s.
-- Test parameters
-- declare	@PhoneNumber varchar(50)
-- set	@PhoneNumber = '1-(301)-887-2403             '
begin
declare	@NewString varchar(50)
declare @Counter int
declare	@TestChar char(1)
set	@NewString = ''
set	@Counter = 1
while	@Counter <= len(@PhoneNumber)
	begin
	set @TestChar = substring(@PhoneNumber, @Counter, 1)
	if ASCII(@TestChar) between 48 and 57 set @NewString = @NewString + @TestChar
	set @Counter = @Counter + 1
	end
while left(@NewString, 1) in ('1', '0') set @NewString = right(@NewString, len(@NewString)-1)
return	nullif(@NewString, '')
end

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-13 : 11:34:57

quote:
Since it is a free-text field, I think you are better off filtering for specific (numeric) characters rather than using replace for all possible dissallowed characters:

Yeah, I thought about that too. I think this will be slower, but is guaranteed to work 'as is'.

humanpuck - I think you will need to decide which is better based on tradeoff.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-13 : 14:06:51

Blindman thats a very nifty function, and I think its perfect for what I want to do. It makes the SQL much cleaner than having multiple replace functions in a select statement. The performance difference is minimal too, thanks again guys this solves my problem nicely.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-13 : 14:43:42

quote:
Originally posted by humanpuck

Blindman thats a very nifty function, and I think its perfect for what I want to do. It makes the SQL much cleaner than having multiple replace functions in a select statement. The performance difference is minimal too, thanks again guys this solves my problem nicely.

Fair enough. Nice work Blindman

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-14 : 09:10:08

Hi guys me again. This solution really was perfect. I see what the function does, and most of the code. The one line I don't really understand is
if ASCII(@TestChar) between 48 and 57 set @NewString = @NewString + @TestChar
can one of you just give me a quick explanation of it. I like getting the end result, but I like understand how I got there. Thanks in advance :)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-14 : 09:20:19

quote:
Originally posted by humanpuck

Hi guys me again. This solution really was perfect. I see what the function does, and most of the code. The one line I don't really understand is
if ASCII(@TestChar) between 48 and 57 set @NewString = @NewString + @TestChar
can one of you just give me a quick explanation of it. I like getting the end result, but I like understand how I got there. Thanks in advance :)

It's just a filter to keep unwanted characters out...ascii values 48 to 57 represents numbers 0 to 9 which are only valid characters we want for phone numbers...so if condition checks whether each character in string is between 0..9.
If it is, it is appended to the result string discarding other characters.

Harsh Athalye
India.
"Nothing is Impossible"

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-14 : 09:26:21

Oh ok that makes sense now...I guess I need to learn my ASCII values :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-07-14 : 09:43:07

here is a nice function: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56713

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-14 : 10:24:15

Pretty nifty...that would've worked too, thats a cool strip function. Thanks again all for the help.

datagod
Starting Member

37 Posts

Posted - 2006-07-14 : 11:03:53

Uh...I don't think you should have posted REAL phone numbers...this is the web afterall...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-14 : 11:19:03

You may want to consider adding constraints to the table or application to only allow phones to in specific formats to be entered. This would go a long ways towards making sure you have clean data. Another thing to consider is another column that specifies the phone number format that is used, and then enforce that the data is in exactly that format.

It's always easier to make sure the data is correct up front than try to figure out later what it should be.

CODO ERGO SUM

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-14 : 11:23:25

quote:
Originally posted by Michael Valentine Jones

You may want to consider adding constraints to the table or application to only allow phones to in specific formats to be entered. This would go a long ways towards making sure you have clean data. Another thing to consider is another column that specifies the phone number format that is used, and then enforce that the data is in exactly that format.

It's always easier to make sure the data is correct up front than try to figure out later what it should be.

CODO ERGO SUM

This is the next step we are taking. I've only been here 1 month, but after seeing this mess we've already implemented the constaints for here on out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources