Select only numeric content - 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 2005 Forums

Transact-SQL (2005)

Select only numeric content

Author

Topic

ranalk
Starting Member

49 Posts

Posted - 2012-06-06 : 03:53:40

Hi,

I have a column that has phone numbers.
There is no validation on insert and some have leading + and some have characters.
What is the efficient way to extract these records the once that are not pure phone number.

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-06 : 04:07:47

To get the rows with non numeric chars you can go like this:
select * from table where phone like '%[^0-9]%'

No, you're never too old to Yak'n'Roll if you're too young to die.

ranalk
Starting Member

49 Posts

Posted - 2012-06-06 : 04:40:05

quote:
Originally posted by webfred

To get the rows with non numeric chars you can go like this:
select * from table where phone like '%[^0-9]%'

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks.

Is there any easy way to substring the prefixes that are not numeric.
Substring should be from the beginning till the first number.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-06 : 06:55:25

If that isn't what you want then please give example data:

select
left(Phone,(patindex('%[0-9]%',Phone))-1),
*
from table
where Phone like '%[^0-9]%'

No, you're never too old to Yak'n'Roll if you're too young to die.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2012-06-06 : 10:30:55

Hi Webfred Nice logic
this is working for first case
Declare @a Table (phone nvarchar(20))
Insert into @a values('9906291558')
Insert into @a values('99062a91558')
Insert into @a values('86506v25854')
--Select * from @a Where phone like ('%[^0-9]%')
select
left(Phone,(patindex('%[^0-9]%',Phone))-1)+
Right(Phone,(patindex('%[^0-9]%',Phone))-1),*
from @a
where Phone like '%[^0-9]%'

--What about this case
Declare @b Table (phone nvarchar(20))
Insert into @b values('9906291558')
Insert into @b values('99062a91558')
Insert into @b values('865062v5854')
select
left(Phone,(patindex('%[^0-9]%',Phone))-1)+
Right(Phone,(patindex('%[^0-9]%',Phone))-1),*
from @b
where Phone like '%[^0-9]%'

--And in This case
Declare @c Table (phone nvarchar(20))
Insert into @c values('9906291558')
Insert into @c values('99062abc91558')
Insert into @c values('86a5062v5854')
select
left(Phone,(patindex('%[^0-9]%',Phone))-1)+
Right(Phone,(patindex('%[^0-9]%',Phone))-1),*
from @c
where Phone like '%[^0-9]%'

--And in this case
Declare @d Table (phone nvarchar(20))
Insert into @d values('+9906291558')
Insert into @d values('99062abc91558')
Insert into @d values('+86a5062v5854')
select
left(Phone,(patindex('%[^0-9]%',Phone))-1)+
Right(Phone,(patindex('%[^0-9]%',Phone))-1),*
from @d
where Phone like '%[^0-9]%'

In Love... With Me!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-06 : 11:05:53

If the question is: get only the numeric part from a string then you can learn so much from madhivanan:
http://beyondrelational.com/modules/2/blogs/70/posts/10821/extract-only-numbers-from-a-string.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-06 : 11:08:54

and this:
http://beyondrelational.com/modules/2/blogs/70/posts/10894/extracting-numbers-part-2.aspx

and this:
http://beyondrelational.com/modules/2/blogs/70/posts/10946/extract-only-numbers-from-a-string-part-3.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2012-06-08 : 02:19:52

quote:
Originally posted by webfred

To get the rows with non numeric chars you can go like this:
select * from table where phone like '%[^0-9]%'

No, you're never too old to Yak'n'Roll if you're too young to die.

Now that my development knowledge is getting a bit rusted ,would the above query same as

select * from table where phone not like '%[0-9]%'

After Monday and Tuesday even the calendar says W T F ....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-08 : 02:41:17

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by webfred

To get the rows with non numeric chars you can go like this:
select * from table where phone like '%[^0-9]%'

No, you're never too old to Yak'n'Roll if you're too young to die.

Now that my development knowledge is getting a bit rusted ,would the above query same as

select * from table where phone not like '%[0-9]%'

After Monday and Tuesday even the calendar says W T F ....

No - it is absolut different.

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2012-06-08 : 02:48:25

Oh sorry I meant

select * from table where phone not like '%[a-z]%'

After Monday and Tuesday even the calendar says W T F ....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-08 : 02:54:52

quote:
Originally posted by Sachin.Nand

Oh sorry I meant

select * from table where phone not like '%[a-z]%'

After Monday and Tuesday even the calendar says W T F ....

To get only rows with non numeric chars the word "not" is wrong there.
But it is still different because [^0-9] captures more than only a-z.

No, you're never too old to Yak'n'Roll if you're too young to die.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-14 : 06:37:40

You can use the following query to exclude '+' and only Select the PhoneNo.:


Select Ltrim(PARSENAME(Replace(Column_Name, '+', '.'), 1))

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources