How to convert RegEx pattern into a SQL SELECT statement for string matching

Question

Need to convert a regex validation pattern used to validate phone numbers into a SELECT query in SQL to validate a list of phone numbers in a SQL table

Regex = ^\s*1?[ \-\(\.\/]*[2-9]\d{2}[ \-\.\(\)\/]*[2-9]\d{2}[ \-\.\(\)\/]*\d{4}\s*$|^\s*011[ \-]*[2-9][ \-\(\.\/\)\,\d]

I have tried the PATINDEX function to pass the above expression. Below is my query

DROP TABLE #PhoneNumberList

SELECT * INTO #PhoneNumberList
FROM (
SELECT '8049901000' AS PhoneNumber, 'Valid' as ValidationResultNeeded 
UNION
SELECT '800-200-1000', 'Valid'
UNION
SELECT '1000900000' , 'Invalid'
UNION 
SELECT '4053366463' , 'Valid'
UNION
SELECT '(405)334-5665' , 'Valid'
UNION
SELECT '405334(6463)' , 'Invalid'
union
SELECT '7341234321' , 'Invalid'
UNION
SELECT '3961573999' , 'Invalid'
UNION
SELECT '40533406463' , 'Invalid'
)A


SELECT * , Patindex('^\s*1?[ \-\(\.\/]*[2-9]\d{2}[ \-\.\(\)\/]*[2-9]\d{2}[ \-\.\(\)\/]*\d{4}\s*$|^\s*011[ \-]*[2-9][ \-\(\.\/\)\,\d]+$',PhoneNumber) AS RegExValidation
FROM #PhoneNumberList
order by 2

Im getting a value of zero for all phone numbers. I was expecting to get a value other than 0 for invalid phone numbers. Am I using this function correctly to get the right validation? Or is there another way to write this SQL statment?

Please tag a DBMS (Oracle, MySQL, etc.). SQL is just a class of languages, not a language in itself, so people can't help without more information. — Josh Eller, Oct 16 '19 at 16:28
I don't know anything about TSQL, but from a quick search, `PATINDEX` only supports basic wild-card matching, not full regex. — Josh Eller, Oct 16 '19 at 16:34
[The T-Sql docs](https://learn.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-ver15) say that `PATINDEX` does not support regular expressions but only elementary wildcards as in `LIKE` expressions. I have no detailed knowledge of T-SQL but it seems that there is no built-in support of regex at all. However, [this SO answer](https://stackoverflow.com/a/30877281) presents a solution to interface with the CLR regex library which should be present on your platform. — collapsar, Oct 16 '19 at 16:45
You may also be interested in [this project](http://igormicev.com/regex-in-sql-server-for-searching-texts/) — collapsar, Oct 16 '19 at 16:51
Thank you all for the response. Im aware of the CLR library, but I was trying to avoid the access permissions i need for this purpose. Ans was looking to get the regular expression converted to a SQL query with the corresponding conditions in the WHERE clause that match the regex validation — Deepa D., Oct 16 '19 at 19:21
Regular expressions are notoriously hard to read. If you could write the validation rules in plain English you will have a better chance of getting a correct answer. Your sample data is great, but does it contain enough cases? — Zohar Peled, Oct 17 '19 at 05:51

score 0 · Answer 1 · answered Oct 22 '19 at 19:19

I'm surprised nobody answered this sooner. this is not too hard. You would need to explain what's wrong with 3961573999 and 7341234321... I'm returning "valid" for these because they appear to be valid phone numbers.

SELECT
  p.PhoneNumber,
  ValidationResultNeeded = 
    CASE WHEN
     (PATINDEX('([2-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]', p.PhoneNumber) ^
      PATINDEX('[2-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]',  p.PhoneNumber) ^
      PATINDEX('[2-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]',    p.PhoneNumber)) <> 0
    THEN 'valid' ELSE 'invalid' END
FROM  #PhoneNumberList AS p;

Results:

PhoneNumber     ValidationResultNeeded
--------------- ----------------------
(405)334-5665   valid
1000900000      invalid
3961573999      valid
405334(6463)    invalid
40533406463     invalid
4053366463      valid
7341234321      valid
800-200-1000    valid
8049901000      valid

How to convert RegEx pattern into a SQL SELECT statement for string matching

1 Answers1