Validating Phone Numbers - 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

Transact-SQL (2000)

Validating Phone Numbers

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-16 : 10:45:07

I have a phone field I need to validate. I need to remove periods, commas, dashes from the phone numbers.

Does anyone know how to write this query?

Thanks,
Ninel

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-08-16 : 10:56:45

You can do some thing like this:
select replace('641-472-1241','-','')

Sanjeev Shrestha
12/17/1971

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-16 : 11:27:34

See my last post here

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 ..."

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-16 : 12:39:43

[code]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[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources