Double digit numeric range regex-CodePudding

I've been trying to write a regex to match for Severity Errors 17 - 25, looking at the error log. (I can provide the full code necessary to get the error log records into a staging table for processing, but I figured it wasn't necessary in relation to the regex)

The closest I've been able to get is...

...
where [Message] like '%Severity: [12][789012345]%'
...

...however, this will end up matching severity errors like 12, 13, 14, 15.. due to the combination of the 1 in the first set the other digits in the second set.

I ended up just writing a set of like statements to achieve it...

...
where [Message] like '%Severity: 17%' 
    or [Message] like '%Severity: 18%' 
    or [Message] like '%Severity: 19%'
    or [Message] like '%Severity: 20%'
    or [Message] like '%Severity: 21%'
    or [Message] like '%Severity: 22%'
    or [Message] like '%Severity: 23%'
    or [Message] like '%Severity: 24%'
    or [Message] like '%Severity: 25%'

Unfortunately SQL Server regex is quite limited, so I can't use the full standard.

Note, even if the execution plan is the same between a regex and multiple like statements, I'd much prefer to use a regex for the sake of code brevity, it looks much cleaner.. unless the performance of multiple like statements is better, in which case I'd prefer performance, as I'm often looking at client's logs with hundreds of thousands or millions of rows.

Is it possible to write a SQL Server regex for a numeric range 17 - 25?

CodePudding user response：

Is it possible to write a SQL Server regex for a numeric range 17 - 25?

Not with native TSQL.

You could write a CLR function to do this though.

Using native TSQL string functions you could potentially parse out the severity value and then test it.

WHERE  TRY_CONVERT(INT, 
                   SUBSTRING([Message], 
                             NULLIF(PATINDEX('%Severity: [1-2][0-9]%', [Message] )   10, 0), 
                             2)
                   ) BETWEEN 17 AND 25

Another alternative to access proper Regex is via the Java language extension but I've never gone that route myself.