Validate phone numbers in SQL

Question

Post reply

Validate phone numbers in SQL

sandhya.msbi

SSC Veteran

Points: 270
More actions
December 15, 2014 at 3:59 am

#301883

Hi All,
I have a table with phone number column, it contains land phone numbers & mobile numbers as well.
1. A phone number without code will be 7 in length
2. A ph number with length 10,11
3. mobile number with length 10,11
Say my phone number code 088 2345678 and mobile number is 2345678919 or 2345678919
data is available in below formats:
1.mob2345678house
2.2345678da
3.2345xx678
4.2345678919this is my number
5.office2345678919
6.2345xhk678919
7.2345678919callme
I want to remove all the alphabets before number starts and after the number and if the number has any alphabets in between the number they should go as invalid records.
Can any body suggest ,how can we achieve With out using any CLRs and using only SQL .
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Have a look at these two threads

😎

How to format unformated phone numbers

Digits only

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

This supports only 4 rules you have mentioned. No '()', '-' or whitespaces allowed.

declare @s-2 varchar(50) = 'fd123456asa789013dfd';

select @s-2

, f.Flag

, nmbr = case f.Flag when 'OK' then substring(@s,f.strt,f.ln) end

from (select null dummy ) dummy

cross apply (

select cd1 = nullif(patindex('%[^0-9][0-9]%',@s),0)

, cde = len(@s) - nullif(patindex('%[0-9][^0-9]%',reverse(@s)),0)

, dc1 = nullif(patindex('%[0-9][^0-9]%',@s),0)

, dce = len(@s) - nullif(patindex('%[^0-9][0-9]%',reverse(@s)),0) ) as pos

cross apply (

select flag = case when @s-2 is null

or patindex('%[0-9]%',@s) = 0

or isnull(pos.dc1,len(@s)) < isnull(pos.cde,0) then 'Bad number'

when isnull(dce,len(@s))-isnull(cd1,0) not in (7,10,11) then 'Bad length'

else 'OK' end

,strt = isnull(cd1,0)+1

,ln = isnull(dce,len(@s))-isnull(cd1,0)

) as f

Hope it helps.

lambad Grasshopper Points: 11 More actions · Answer 3

How i can use a column name instead of providing number, i have phone numbers provided in column "TELEPHONE" on table "ADDRESS", so how i can use below query to validate whole column in once.

this is great and i was looking for this solution, please help

declare @s-2 varchar(50) = 'fd123456asa789013dfd';

select @s-2

, f.Flag

, nmbr = case f.Flag when 'OK' then substring(@s,f.strt,f.ln) end

from (select null dummy ) dummy

cross apply (

select cd1 = nullif(patindex('%[^0-9][0-9]%',@s),0)

, cde = len(@s) - nullif(patindex('%[0-9][^0-9]%',reverse(@s)),0)

, dc1 = nullif(patindex('%[0-9][^0-9]%',@s),0)

, dce = len(@s) - nullif(patindex('%[^0-9][0-9]%',reverse(@s)),0) ) as pos

cross apply (

select flag = case when @s-2 is null

or patindex('%[0-9]%',@s) = 0

or isnull(pos.dc1,len(@s)) < isnull(pos.cde,0) then 'Bad number'

when isnull(dce,len(@s))-isnull(cd1,0) not in (7,10,11) then 'Bad length'

else 'OK' end

,strt = isnull(cd1,0)+1

,ln = isnull(dce,len(@s))-isnull(cd1,0)

) as f

This reply was modified 3 years, 1 month ago by lambad.

ScottPletcher SSC Guru Points: 100946 More actions · Answer 4

SELECT 
    phone_string,
    phone,
    --LEN(phone) AS phone_len,
    CASE WHEN 
    CASE WHEN phone LIKE '%[^0-9]%' THEN 0
         WHEN LEN(phone) IN (7, 10, 11) THEN 1 ELSE 0 END
    = 0 THEN 'Invalid' ELSE 'Valid' END AS phone_status

FROM ( VALUES
    (1, 'mob2345678house'), (2, '2345678da'), (3, '2345xx678'),
    (4, '2345678919this is my number'), (5, 'office2345678919'),
    (6, '2345xhk678919'), (7, '2345678919callme'), 
    (8, 'bunchoflettersnonumber') --<<--added row with no digits at all
    ) AS  data(id, phone_string)
CROSS APPLY (
    SELECT PATINDEX('%[0-9]%', phone_string) AS phone_first_digit,
        LEN(phone_string) - PATINDEX('%[0-9]%', REVERSE(phone_string)) + 1 AS phone_last_digit        
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN phone_first_digit = 0 THEN '' ELSE SUBSTRING(phone_string, 
        phone_first_digit, phone_last_digit - phone_first_digit + 1) END AS phone
) AS ca2

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.