Update Where Values don't match Regex format in management studio 2012

Question

I need to validate UK phone numbers, essentially removing any which are invalid. I'd love to validate them at the front end but that isn't possible sadly.

Having spent a long time looking, I found a wonderful answer here: Validate a UK phone number provding possible regex of: /^(?0( *\d)?){9,10}$/

and then from this question (and others):Validate telephone number in SQL Server 2000 The suggestion of using xp_pcre: http://www.codeproject.com/Articles/4733/xp-pcre-Regular-Expressions-in-T-SQL to enable and use the regex, however this isn't compatible with 64-bit.

So my question is how do I do an update statement on fields where the values don't match a regex format.

That regex allows `0 1) 2) 3) 4) 5) 6) 7) 8) 9) 0` as a valid number so I wouldn't class that as "wonderful" — Martin Smith, Nov 07 '15 at 19:41
@MartinSmith It checks for the correct length, that it starts with a 0 and allows spaces, all which is fine as though I can't change the front end I know only numbers are allowed through. I also mainly mentioned that previous question as to not be accused of having not tried looking for an answer before asking. — Vereonix, Nov 07 '15 at 19:45
SQL Server doesn't support regular expressions, but you can use a [CLR function](https://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/) to expose .NET regular expressions to SQL queries — Andomar, Nov 07 '15 at 19:54

JohnLBevan · Accepted Answer · 2015-11-07T20:44:45.763

Here's one solution using standard SQL functionality (i.e. without CLR functions):

create function dbo.PhoneNoIsValid
(@number nvarchar(20))
returns bit
begin
    --use an innocent until proven guilty approach
    --once proven guilty, skip further checks by adding  
    --`if @isValid = 1 and` before further checks 
    declare @isValid bit = 1
    --no strict rules around spaces; they are allowed but  
    --don't add anything
    --by removing them we simplify the patterns we need to check
    set @number = REPLACE(@number,' ','')
    --aside from spaces, only numbers, brackets, and the plus  
    --sign are valid chars
    if @number like '%[^\+\(\)0-9]%' 
        set @isValid = 0
    --min length of a valid phone number is 11 chars
    if @isValid = 1 and LEN(@number) < 11  
        set @isValid = 0
    --the area code (minus leading zero (or similar) plus the  
    --local code are only numbers (and spaces; removed earlier)  
    --so we can check for invalid chars.
    if @isValid = 1 and SUBSTRING(@number,LEN(@number)-9,10) like '%[^0-9]%'  
        set @isValid = 0
    --now we've validated the last bit, remove it so we can  
    --focus on the first bit
    if @isValid = 1  
        set @number = SUBSTRING(@number,1, LEN(@number)-10)
    --given we're using a UK number there are limited options;  
    --so simplest to just enumerate these and check against  
    --each valid option
    if @number not in ('0','0044','+44','+44(0)','0044(0)')  
        set @isValid = 0
    --that's all the checks I can think of; at this stage the  
    --number's valid or has been proven invalid.
    return (@isValid)
end

Example Usage:

declare @sampleData table 
(
    phoneNo nvarchar(20)
    , isValid bit default(1)
)
insert @sampleData 
(phoneNo)
values ('0044 1234 567890')
, ('+44 1234 567891')
, ('+44 (0)1234 567892')
, ('0044 (0)1234 567892')
, ('01234 567893')
, ('00441234567890')
, ('+441234567891')
, ('+44(0)1234567892')
, ('0044(0)1234567892')
, ('01234567893')
insert @sampleData 
(isValid, phoneNo)
values (0,'0044 1234 56780')
, (0,'+44 1234 56781')
, (0,'+44 (0)1234 56782')
, (0,'044 (0)1234 567893')
, (0,'1234 567894')
, (0,'234567895')
, (0,'0044123456786')
, (0,'+44123456787')
, (0,'+44(0)123456788')
, (0,'044(0)1234567899')
, (0,'1234567810')
, (0,'234567811')
--select * from @sampleData 

--demo
select *
from @sampleData 
where dbo.PhoneNoIsValid(phoneNo) != isValid --show where I've got something wrong

--update statement
update @sampleData
set phoneNo = ''
where dbo.PhoneNoIsValid(phoneNo)= 0

select isValid, COUNT(1) from @sampleData group by isValid order by isValid
select isValid, COUNT(1) from @sampleData where phoneNo = '' group by isValid order by isValid

NB: I've assumed that when you say "valid UK phone number" you mean a phone number that's valid for a phone in the UK; as opposed to a number that's valid to call from the UK (i.e. this would show US phone numbers as invalid).

Update Where Values don't match Regex format in management studio 2012

1 Answers1