Using RegEx in SQL Server

Question

I am looking for how to replace / encode text with RegEx based on RegEx settings / options below:

RegEx.IgnoreCase = True RegEx.Global = True RegEx.Pattern = "[^az\d\s.]+" 

I saw a few examples in RegEx, but am confused about how to apply it in the same way in SQL Server. Any suggestions would be helpful. Thank.

Answer 1

You do not need to interact with managed code, since 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 

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

EDIT : make it clear: SQL Server does not support regular expressions without managed code. Depending on the situation, the LIKE operator may be an option, but it lacks the flexibility that regular expressions provide.

Answer 2

You will need to create a CLR procedure that provides regular expression functions, as shown in this article .

Answer 3

A slightly modified version of Julio's answer.

 -- MS SQL using VBScript Regex -- select dbo.RegexReplace('aa bb cc','($1) ($2) ($3)','([^\s]*)\s*([^\s]*)\s*([^\s]*)') -- $$ dollar sign, $1 - $9 back references, $& whole match CREATE FUNCTION [dbo].[RegexReplace] ( -- these match exactly the parameters of RegExp @searchstring varchar(4000), @replacestring varchar(4000), @pattern varchar(4000) ) RETURNS varchar(4000) AS BEGIN declare @objRegexExp int, @objErrorObj int, @strErrorMessage varchar(255), @res int, @result varchar(4000) if( @searchstring is null or len(ltrim(rtrim(@searchstring))) = 0) return null set @result='' exec @res=sp_OACreate 'VBScript.RegExp', @objRegexExp out if( @res <> 0) return '..VBScript did not initialize' exec @res=sp_OASetProperty @objRegexExp, 'Pattern', @pattern if( @res <> 0) return '..Pattern property set failed' exec @res=sp_OASetProperty @objRegexExp, 'IgnoreCase', 0 if( @res <> 0) return '..IgnoreCase option failed' exec @res=sp_OAMethod @objRegexExp, 'Replace', @result OUT, @searchstring, @replacestring if( @res <> 0) return '..Bad search string' exec @res=sp_OADestroy @objRegexExp return @result END 

You will need the Ole automation procedures included in SQL:

 exec sp_configure 'show advanced options',1; go reconfigure; go sp_configure 'Ole Automation Procedures', 1; go reconfigure; go sp_configure 'show advanced options',0; go reconfigure; go 

Answer 4

 SELECT * from SOME_TABLE where NAME like '%[^AZ]%' 

Or another expression instead of AZ

Answer 5

You can use the function in SqlServer and pass it the value you want to evaluate. Have a look at this link: http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs .80) .aspx