CODEWITHSUNDEEP
sql-servervalidationmobile
user3207341
user3207341

Reputation: 69

SQL mobile number vaildation

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]?

Upvotes: 0

Views: 3807

Answers (1)

Dave Mason
Dave Mason

Reputation: 4936

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]'

Upvotes: 1

Related Questions