SQL mobile number vaildation

Question

I have my table within SQL server 2008 and one of the fields is Mobile Phone. Is there a Where statement i can use within sql that will return only Validated Mobile numbers such as;

+44123456789,
0044123456789,
123456789
123-456789

etc. I can use this '^(((((\d{3}))|(\d{3}-))\d{3}-\d{4})|(+?\d{2}((-| )\d{1,8}){1,5})){0,1}$'

but how can i exclude the brackets () selection within the number

also if i wanted the beginning of the number to begin with an '087' would i just input [0][8][7] instead of the [0-9]?

You could use regex in SQL-Server. Patterns [here](http://stackoverflow.com/questions/123559/a-comprehensive-regex-for-phone-number-validation), how to use regex in T-SQL [here](http://msdn.microsoft.com/en-us/magazine/cc163473.aspx) — Tim Schmelter, Mar 07 '14 at 13:58
If you dont mind can you be clear about question and add some more data? — user3203331, Mar 07 '14 at 13:58
I can use this '^(((((\d{3}))|(\d{3}-))\d{3}-\d{4})|(+?\d{2}((-| )\d{1,8}){1,5})){0,1}$' but how can i exclude the brackets () selection within the number — user3207341, Mar 07 '14 at 14:31
You can't use Regex directly in tsql. You'd have to use a CLR function to access Regex from the .Net Framework, or use an extended stored procedure. — Dave Mason, Mar 07 '14 at 14:44

Dave Mason · Accepted Answer · 2014-03-07T14:45:01.260

1

Here's a possible solution, using only tsql.

CREATE TABLE #PhoneNumbers (
    ID INT IDENTITY,
    PhoneNumber VARCHAR(32)
)

INSERT INTO #PhoneNumbers (PhoneNumber)
VALUES 
    ('+44123456789'),
    ('0044123456789'),
    ('123456789'),
    ('123-456789'),

    --Invalid numbers for testing.
    ('12-3456789'),
    ('123456789+'),
    ('123abc789')

SELECT *
FROM #PhoneNumbers pn

--Where clause filters, based on the four scenarios provided.
WHERE pn.PhoneNumber LIKE '+[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
OR pn.PhoneNumber LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
OR pn.PhoneNumber LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
OR pn.PhoneNumber LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9]'

edited Mar 07 '14 at 14:45

answered Mar 07 '14 at 14:37

Dave Mason

4,746
2
23
24

Thanks for that DMason, Quick qt, if i wanted the beginning of the number to begin with an '087' would i just input [0][8][7] instead of the [0-9]? – user3207341 Mar 10 '14 at 09:50
You can just use '087'. For instance, instead of `pn.PhoneNumber LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9]'` it would be `pn.PhoneNumber LIKE '087-[0-9][0-9][0-9][0-9][0-9][0-9]'` – Dave Mason Mar 10 '14 at 13:33
Thanks that works however I tried to do the same for 003538%' and LEN(Customer.MobilePhone) = 14 but is doesn't seem to be picking it up? – user3207341 Mar 19 '14 at 11:46

SQL mobile number vaildation

1 Answers1