Extract Phone Number String

17 Feb 2006 3:26 PM

Tom

Hello Experts:

I am a newbie to T-SQL, and I wonder if this is possible. I have the
following possible phone number strings coming in:

1. 8882223333
2. 18882223333
3. +8882223333
4. 888 222 3333

What the expected end result is case #1: 8882223333. Is it efficient for
T-SQL to process strings? How would it be done it in T-SQL?

--
Thanks,

Tom

17 Feb 2006 3:35 PM

Andrew J. Kelly

If you are using SQL2005 you can write a pretty efficient CLR UDF to handle
the string parsing but TSQL itself is not the best. If you are on 2000 then
you may want to preprocess this data on the front end.

--
Andrew J. Kelly SQL MVP

Show quote

"Tom" <T**@discussions.microsoft.com> wrote in message
news:3C0899C4-488C-4073-9BEE-40BA5D9F072F@microsoft.com...
> Hello Experts:
>
> I am a newbie to T-SQL, and I wonder if this is possible. I have the
> following possible phone number strings coming in:
>
> 1. 8882223333
> 2. 18882223333
> 3. +8882223333
> 4. 888 222 3333
>
> What the expected end result is case #1: 8882223333. Is it efficient for
> T-SQL to process strings? How would it be done it in T-SQL?
> --
> Thanks,
>
> Tom

17 Feb 2006 3:58 PM

Tom

Andrew:

Thanks for your response. Yes, you are right, it would be logical to process
the string on the front end. However, the problem is that these phone numbers
are coming to one central database, which is SQL Server 2000, from different
systems running on different platforms. Processing them once at the database
seems to be the best approach. Please help.

--
Thanks,

Tom

Show quote

"Andrew J. Kelly" wrote:

> If you are using SQL2005 you can write a pretty efficient CLR UDF to handle
> the string parsing but TSQL itself is not the best. If you are on 2000 then
> you may want to preprocess this data on the front end.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Tom" <T**@discussions.microsoft.com> wrote in message
> news:3C0899C4-488C-4073-9BEE-40BA5D9F072F@microsoft.com...
> > Hello Experts:
> >
> > I am a newbie to T-SQL, and I wonder if this is possible. I have the
> > following possible phone number strings coming in:
> >
> > 1. 8882223333
> > 2. 18882223333
> > 3. +8882223333
> > 4. 888 222 3333
> >
> > What the expected end result is case #1: 8882223333. Is it efficient for
> > T-SQL to process strings? How would it be done it in T-SQL?
> > --
> > Thanks,
> >
> > Tom
>
>
>

17 Feb 2006 4:05 PM

SQL

not foolproof but will work if all tel numbers are int these 4 formats
only

create table #tel(telnum varchar(50))
insert into #tel
select'8882223333' union all
select '18882223333' union all
select '+8882223333' union all
select '888 222 3333'

select telnum,right(replace(replace(telnum,' ',''),'+',''),10)
from #tel

http://sqlservercode.blogspot.com/

Extract Phone Number String - Please Help!