0

I am creating a validation script and need help with mobile number validation logic with below details. Mobile number must be:

  • 10 digits in length.
  • Cannot begin with a 1 or a 0 (For example:1144684679 or 04468-4679
  • Cannot contain all the same digit. For example: 5555555555

I am using PATINDEX for this but could not come up with the proper result.

Improve this question
7
  • 1
    Does this have to be done strictly in T-SQL, or do you have the option of performing this validation in another language (a C# component in an SSIS package or a full application, for example), or via a platform like Master Data Services or even a service/external API? How do you intend to handle people (like me) whose phone number isn't a direct line, but a 10-digit number plus a local extension once you connect to my company's phone system?
    – alroc
    Commented Apr 30, 2020 at 11:54
  • Are non-numeric characters allowed? Like dashes?
    – Scott Hodgin - Retired
    Commented Apr 30, 2020 at 12:16
  • Are you sure there will never be international phone numbers? It's usually much easier to do application level validation instead of pushing it into the database.
    – vonPryz
    Commented Apr 30, 2020 at 12:18
  • In the UK all mobile numbers begin with a zero. What locale are these rules valid for?
    – Martin Smith
    Commented Apr 30, 2020 at 13:12
  • Simply because a value matches an expected format does not make it "valid" (as in, it is an number that is currently in service in some undefined location / country).
    – SMor
    Commented Apr 30, 2020 at 14:42

1 Answer 1

Reset to default
1

I seriously doubt the real world utility of the specified validation rules but a reasonably concise way of validating them is below. 

SELECT PhoneNumber,
       Valid = CASE
                 WHEN PhoneNumber LIKE CONCAT('[23456789]', REPLICATE('[0123456789]', 9))
                      AND PhoneNumber LIKE CONCAT('%[^', LEFT(PhoneNumber, 1), ']%')
                   THEN 'valid'
                 ELSE 'invalid'
               END
FROM   (VALUES ('0123456789'), 
               ('1144684679'), 
               ('5555555555'), 
               ('5555555554'), 
               ('9876543210'), 
               ('9876')) PhoneNumbers(PhoneNumber) 

+-------------+---------+
| PhoneNumber |  Valid  |
+-------------+---------+
|  0123456789 | invalid |
|  1144684679 | invalid |
|  5555555555 | invalid |
|  5555555554 | valid   |
|  9876543210 | valid   |
|        9876 | invalid |
+-------------+---------+
Improve this answer
5
  • 1
    Martin. This answer is amazing by the way. I think understand what the first part of the validation is doing. In the 2nd part LIKE CONCAT('%[^', LEFT(PhoneNumber, 1), ']%') the validation to make sure it isn't the same number for every character? I am not super familiar with utilizing REGEX.
    – Kirk Saunders
    Commented Apr 30, 2020 at 16:30
  • 1
    Thanks - and yes that's what it is doing. If the first character is 5 the expression will be LIKE '%[^5]%' which means that the phonenumber needs to contain a character that is not 5. Could also use PhoneNumber <> REPLICATE(LEFT(PhoneNumber,1), 10)
    – Martin Smith
    Commented Apr 30, 2020 at 16:32
  • Thanks @MartinSmith I have checked this and its working fine for me .
    – deep kachhawa
    Commented May 4, 2020 at 4:34
  • Hey @MartinSmith what about below logic as it is giving me same output SELECT * FROM DemoContact WHERE NOT PATINDEX('%[^01][^01][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',Contact)=0 AND Contact <> REPLICATE(LEFT(Contact,1),10) (DemoContact is a table which has contact column,)
    – deep kachhawa
    Commented May 4, 2020 at 4:42
  • ^01 just excludes 0 and 1, this means that it will allow non numbers which isn't what you need...
    – Martin Smith
    Commented May 4, 2020 at 8:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.