Using RegEx in SQL Server

Question

I'm looking how to replace/encode text using RegEx based on RegEx settings/params below:

RegEx.IgnoreCase = True      RegEx.Global = True      RegEx.Pattern = "[^a-z\d\s.]+"    

I have seen some examples on RegEx, but confused as to how to apply it the same way in SQL Server. Any suggestions would be helpful. Thank you.

Answer 1

You do not need to interact with managed code, as you can use LIKE:

CREATE TABLE #Sample(Field varchar(50), Result varchar(50)) GO INSERT INTO #Sample (Field, Result) VALUES ('ABC123 ', 'Do not match') INSERT INTO #Sample (Field, Result) VALUES ('ABC123.', 'Do not match') INSERT INTO #Sample (Field, Result) VALUES ('ABC123&', 'Match') SELECT * FROM #Sample WHERE Field LIKE '%[^a-z0-9 .]%' GO DROP TABLE #Sample 

As your expression ends with + you can go with '%[^a-z0-9 .][^a-z0-9 .]%'

EDIT:
To make it clear: SQL Server doesn't support regular expressions without managed code. Depending on the situation, the LIKE operator can be an option, but it lacks the flexibility that regular expressions provides.