Using RegEx in SQL Server

Using RegEx in SQL Server

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

Regular Expressions In SQL Server Databases Implementation Use

Regular Expression – Description
. Match any one character
* Match any character
+ Match at least one instance of the expression before
^ Start at beginning of line
$ Search at end of line
< Match only if word starts at this point
> Match only if word stops at this point
n Match a line break
[] Match any character within the brackets
[^…] Matches any character not listed after the ^
[ABQ]% The string must begin with either the letters A, B, or Q and can be of any length
[AB][CD]% The string must have a length of two or more and which must begin with A or B and have C or D as the second character
[A-Z]% The string can be of any length and must begin with any letter from A to Z
[A-Z0-9]% The string can be of any length and must start with any letter from A to Z or numeral from 0 to 9
[^A-C]% The string can be of any length but cannot begin with the letters A to C
%[A-Z] The string can be of any length and must end with any of the letters from A to Z
%[%$#@]% The string can be of any length and must contain at least one of the special characters enclosed in the bracket

Using RegEx in SQL Server

Slightly modified version of Julios 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

Youll need Ole Automation Procedures turned on 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

Leave a Reply

Your email address will not be published.