Sql Like to RegEx

Question

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]

Solution 1

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.

Solution 2

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

PATINDEX (Transact-SQL)

Solution 3

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.

Solution 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.

Solution 5

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

View more solutions

Comments

[Andreas]

Is there a way to tell the function that multiple instances of a expression, like regex:s "+" or "*". For example PATINDEX('%[a-c]+%',@Input)

[Andreas]

Finally got an answer from them, should work fine.. Thanks. And even though your answer don't quite answer my question, it solves my problem.. So I mark it as solved :)

[Nerdroid]

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes