Sql Like to RegEx

Question

Hi,

Is there a good way to convert Regular Expression into LIKE inside a Function (MSSQL)? The sproc does not get more complicated than this:

(country\=)(?<Country>[\w\d]+)(\&sessionid\=)(?<SessionId>.+)

The groups will not be used in the LIKE, they are there for another purpose.

I would like to use this inside a sproc late like:

SELECT * FROM [Table]
WHERE test_regex(regex, 'text') = 1

Where the regex is a part of [Table]

Answer 1

Check this functin of sql may help you to achieve your task : PATINDEX('%[^0-9\.]%',@Input)

PATINDEX (Transact-SQL)

Answer 2

Not out of the box. The closest you get to a regex search in SQL Server is PATINDEX(), but that doesn't do regular expressions.

If you really wanted this, you'd have to make a CLR function to run the regex instead. You may be concerned about speed, but I would say that almost nothing you would do which concerns regular expressions or patterns would be able to use an index anyway.

Answer 3

It is possible to add Regular Expression parsing to SQL server, using a CLR function. This is possible from SQL 2005 and up. See here for a great example.

Answer 4

that will run very slow on a large table, parsing the string column on each and every row.

if you can change the table, it might be better to split that string column into different columns, so you can have a more traditional WHERE country=... AND sessionid=..., you could even add and use an index this way.

Answer 5

I'd imagine

SELECT ...
FROM [Table]
WHERE col LIKE 'country=[A-Za-z0-9]%&sessionid=[A-Za-z0-9]%'

might be close enough? If the aim is just to bring back records with non blank country and sessionid values.

If the value in the column wouldn't necessarily start with 'country' you'd have to use WHERE col LIKE '%country=[A-Za-z0-9]%&sessionid=[A-Za-z0-9]%' which could slow things down considerably as per KM's answer.

Answer 6

Use the RegEx functionality within SQL# which is free. I've not had any performance issues with this approach.