CODEWITHSUNDEEP
t-sqlwildcard
Bjørn H. Sandvik
Bjørn H. Sandvik

Reputation: 543

Complex RegEx in T-SQL

I recently started using RegEx as conditional in my queries, but it seems that T-SQL has limited support for the official syntax.

As an example, I wish to test if a string is valid as a time between 00:00 and 23:59, and a fine RegEx expression would be "([0-1][0-9]|[2][0-3]):([0-5][0-9])":

select iif('16:06' like '([0-1][0-9]|[2][0-3]):([0-5][0-9])', 'Valid', 'Invalid')

.. fails and outputs "Invalid". Am I right to understand that T-SQL cannot handle groupings and conditionals (|)? I wound up lazily using a simplified RegEx which does not properly test the string - which I am fairly unhappy with:

select iif('16:06' like '[0-2][0-9]:[0-5][0-9]', 'Valid, 'Invalid')

.. which returns "Valid", but would also consider the string "28:06" as valid.

I know I can add further checks to fully check if it is a valid time string, but I would much prefer to take full advantage of RegEx.

Simply asked: Am I just doing or thinking things wrong about this being a limitation, and if yes - how can I use proper RegEx in T-SQL?

Upvotes: 2

Views: 5961

Answers (3)

LukStorms
LukStorms

Reputation: 29677

The pattern syntax used for LIKE and PATINDEX is much more limited than what's commonly known as Regular Expressions.

In standard SQL it actually has only 2 special characters.

% : wildcard for 0 or more characters
_ : any 1 character

And T-SQL added the character class [...] to the syntax.

But to test if a string contains a time, using LIKE is a clumsy way to do it.

In MS Sql Server one can use the TRY_CONVERT or TRY_CAST functions. They'll return NULL is the conversion to a datatype fails.

select IIF(TRY_CAST('16:06' AS TIME) IS NOT NULL, 'Valid', 'Invalid')

This will return 'Valid' for '23:59', but 'Invalid' for '24:00'

Upvotes: 4

Igor N.
Igor N.

Reputation: 434

I would recommend writing a user defined function using SQLCLR. Since .Net supports Regex you can port it to T-SQL. First link in Google gave this implementation, but there may be other (better) implementations.

Caveat - use of SQLCLR requires elevated permissions and may lead to security issues or performance issues or even issues with stability of the SQL Server if not implemented correctly. But if you know what you are doing this may lead to significant enhancements of T-SQL specific for your use cases.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

You may use the following logic:

SELECT IIF('16:06' LIKE '[01][0-9]:[0-5][0-9]' OR
           '16:06' LIKE '2[0-3]:[0-5][0-9]', 'Valid', 'Invalid');

The first LIKE expression matches 00:00 to 19:59, and the second LIKE matches 20:00 to 23:59. If SQL Server supported full regex, we could just use a single regex expression with an alternation.

Upvotes: 3

Related Questions