I want to write a function in Microsoft SQL Server to validate any UK Phone numbers and flag 1 if valid and flag 0 if invalid

Question

This is my code:

FUNCTION [dbo].[Vmobile](@mobileno VARCHAR(50))  
    --Returns true if the string is a valid mobile no.  
RETURNS bit  
AS
BEGIN  
     DECLARE @valid bit  

     IF @mobileno IS NOT NULL   
         SET @valid = 0  

     IF @mobileno like '^(?:0|\+?44)(?:\d\s?){9,10}$'     
         SET @valid=1  

     RETURN @valid  
END

The phone numbers can be in any UK format but it should support all UK Phone numbers. E

g :
(415) 555-0132
+44 313314332
098 765 1234
+447975777666
07975777666
xxx-xxx-xxxx
01332 412251
01332 412 251
+44 1332 412251

where X can be any number. Optional Plus can be allowed at first position only. It should have 9 or 10 digit after zero if zero is entered in the beginning. country code can be optional

Please explain what UK phone numbers look like, for the other 99.1% of the world (assuming that all UK inhabitants know all the different formats). — Gordon Linoff, Jul 15 '21 at 15:24
Is your code not working as you want it to? Throwing an error? Other? — Andrew, Jul 15 '21 at 15:37
@GordonLinoff I have added some examples . could you please check on that? — john, Jul 15 '21 at 16:57
Does this answer your question? [Using RegEx in SQL Server](https://stackoverflow.com/questions/8928378/using-regex-in-sql-server) — Charlieface, Jul 15 '21 at 18:59

score 0 · Answer 1 · answered Jul 15 '21 at 15:41

0

It looks like you'll have to start again. You appear to be using a regular expression to test the phone number, and the SQL Server LIKE operator does not afaik support regular expressions.

If you're using a DBMS that does support regular expressions, you'll have better luck getting a useful answer if you link to the regex documentation and, as @Gordon Linoff suggests, explain the pattern you're trying to match. Also, please edit the tags not to mention SQL, because this functionality isn't defined by the SQL standard, and instead add the particular DBMS you're using.

answered Jul 15 '21 at 15:41

James K. Lowden

7,574
1
16
31

I have reframed my question . I want to create a function in MS SQL Server 2019 to validate mobile numbers in UK. I have added some examples and my requirements. – john Jul 15 '21 at 17:00
Could you please help me with the like operator for my above requirements? – john Jul 15 '21 at 18:53

I want to write a function in Microsoft SQL Server to validate any UK Phone numbers and flag 1 if valid and flag 0 if invalid

1 Answers1