| Author |
 Topic  |
|
|
ranalk
Starting Member
48 Posts |
 Posted - 06/06/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 06/06/2012 : 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
48 Posts |
Posted - 06/06/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 06/06/2012 : 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
India
277 Posts |
Posted - 06/06/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 06/08/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 06/08/2012 : 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
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 06/08/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 06/08/2012 : 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. |
Edited by - webfred on 06/08/2012 02:57:08 |
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/14/2012 : 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" |
|
|
| |
Topic |
|
Select only numeric content
