Do you know how to check a phone number?

Of course I do! Here’s a little nifty function you can use or expand for your own needs. This was made for Dutch mobile phone numbers but can be easily tweaked for any other type of phone number.

create or alter function [dbo].[tvf_FormatPhoneNumber]
(
    @PhoneNumber nvarchar(max)
)
returns table
as
return
(
with LoopForDigits (number) as
(
    select
        substring(@PhoneNumber , NMB.number, 1) as [text()]
    from 
        master..spt_values as NMB
    where 
        substring(@PhoneNumber , NMB.number, 1) like '[0-9]'
    and NMB.number between 0 and len(@PhoneNumber )
    and NMB.type = 'P'
    for 
        xml path ('')
),
PhonePattern as
(
    select
        pattern = '%6[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
)
select
    PhoneNumber = concat('31', substring(DIG.number, patindex(PHP.pattern, DIG.number), 9))
from 
    LoopForDigits as DIG
        inner join 
    PhonePattern PHP
        on patindex(PHP.pattern, DIG.number) > 0
)

create or alter function [dbo].[tvf_FormatPhoneNumber]

(

@PhoneNumber nvarchar(max)

)

returns table

return

(

with LoopForDigits (number) as

(

select

substring(@PhoneNumber , NMB.number, 1) as [text()]

from

master..spt_values as NMB

where

substring(@PhoneNumber , NMB.number, 1) like '[0-9]'

and NMB.number between 0 and len(@PhoneNumber )

and NMB.type = 'P'

for

xml path ('')

PhonePattern as

(

select

pattern = '%6[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

)

select

PhoneNumber = concat('31', substring(DIG.number, patindex(PHP.pattern, DIG.number), 9))

from

LoopForDigits as DIG

inner join

PhonePattern PHP

on patindex(PHP.pattern, DIG.number) > 0

)

The way this works is as follows.

First we split the string into a table of single characters. We then filter the ones that aren’t numeric. We use the like function because isnumeric would not filter anything that is used to represent a number like ‘-‘ or ‘+’ in the phone numbers. Finally we concatenate with the xml-trick. Then, we select the part of the resulting string that matches our pattern.

Change the pattern to suit your country’s phone number style – or you can just do the check and return a boolean to indicate it’s actually a valid phone number or not.