Complex RegEx in T-SQL-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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 may 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')