Using RegEx in SQL Server

Question

I am trying to replace/encode the text using RegEx based on RegEx settings/params following:

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

I saw some examples on RegEx however I am very confused as to how to apply it the same way in the SQL Server. 

Can anyone help with this?

Answer 1

You don't have to interact with the managed code, because 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 .]%'

The SQL Server does not support regular expressions without the managed code. Though it is depending on the condition, this LIKE operator can be a choice, though, it lacks the flexibility that regular expressions can provide.

Want to become an expert in SQL? Join the SQL course fast!