THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Pattern-based replacement UDF

As a personal challenge, I decided to write a UDF that will work just like T-SQL's REPLACE() function, but using patterns as input.

The first question: How does REPLACE() handle overlapping patterns?


SELECT REPLACE('babab', 'bab', 'c')


(1 row(s) affected)

SELECT REPLACE('bababab', 'bab', 'c')


(1 row(s) affected)

It appears that SQL Server parses the input string from left to right, replacing the first instance of the replacement string, and then continues parsing to the right.

Next question: How to do the replacement on a pattern? As it turns out, this is somewhat trickier than I initially thought. A replacement requires a starting point -- easy to find using PATINDEX -- and an end point. But there is no function for finding the last character of a pattern. So you'll see that the UDF loops character-by-character, testing PATINDEX, in order to find the end of the match. This is useful for situations like:


SELECT dbo.PatternReplace('baaa', 'ba%', 'c')

-- We know that the match starts at character 1... but where does it end?

Anyway, enough background, here's the code:


CREATE FUNCTION dbo.PatternReplace
@InputString VARCHAR(4000),
@Pattern VARCHAR(100),
@ReplaceText VARCHAR(4000)
DECLARE @Result VARCHAR(4000) SET @Result = ''
-- First character in a match
-- Next character to start search on
DECLARE @Next INT SET @Next = 1
-- Length of the total string -- 8001 if @InputString is NULL
DECLARE @Len INT SET @Len = COALESCE(LEN(@InputString), 8001)
-- End of a pattern

WHILE (@Next <= @Len)
SET @First = PATINDEX('%' + @Pattern + '%', SUBSTRING(@InputString, @Next, @Len))
IF COALESCE(@First, 0) = 0 --no match - return
SET @Result = @Result +
CASE --return NULL, just like REPLACE, if inputs are NULL
WHEN @InputString IS NULL
OR @Pattern IS NULL
ELSE SUBSTRING(@InputString, @Next, @Len)
-- Concatenate characters before the match to the result
SET @Result = @Result + SUBSTRING(@InputString, @Next, @First - 1)
SET @Next = @Next + @First - 1

SET @EndPattern = 1
-- Find start of end pattern range
WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) = 0
SET @EndPattern = @EndPattern + 1
-- Find end of pattern range
WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) > 0
AND @Len >= (@Next + @EndPattern - 1)
SET @EndPattern = @EndPattern + 1

--Either at the end of the pattern or @Next + @EndPattern = @Len
SET @Result = @Result + @ReplaceText
SET @Next = @Next + @EndPattern - 1

... And here's how you run it, with some sample outputs showing that it does, indeed, appear to work:


SELECT dbo.PatternReplace('babab', 'bab', 'c')


(1 row(s) affected)

SELECT dbo.PatternReplace('babab', 'b_b', 'c')


(1 row(s) affected)

SELECT dbo.PatternReplace('bababe', 'b%b', 'c')


(1 row(s) affected)

Hopefully this will help someone, somewhere. I haven't found any use for it yet :)

Thanks to Steve Kass for posting some single-character replacement code which I based this UDF on.


Update, January 10, 2005: Thanks to Frank Kalis, I've tracked down some problems with the original UDF. The version posted here has been fixed and now should respond identically to the T-SQL REPLACE function when NULLs or non-pattern-based arguments are passed in. The following example pairs should return the same values (and do, at this point!)


SELECT dbo.PatternReplace(NULL, '', 'abc')

SELECT dbo.PatternReplace('abc', '', NULL)

SELECT dbo.PatternReplace('abc', NULL, '')

SELECT dbo.PatternReplace('abc', 'b', '')
SELECT REPLACE('abc', 'b', '')

SELECT dbo.PatternReplace('adc', 'b', '')
SELECT REPLACE('adc', 'b', '')
Published Wednesday, July 12, 2006 10:07 PM by Adam Machanic
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Gabriel Koscky said:

Thanks a lot for this function!

Would you mind if I use it in a function to clean SQL Injected databases? It's very useful to search for script tags and remove them.

July 29, 2008 4:22 PM

Adam Machanic said:

You can do whatever you want with it, short of selling it without giving me a chunk of the profit :-)

August 1, 2008 3:18 PM

Vineet Dave said:

That is awesome piece of code.

April 9, 2009 6:17 PM

Sharmin Jose said:

Hi Adam,

I have a similar kind of requirement for my Quiz project but a different way. In my DB, I have texts which are similar as below:

"You selected the option as [QuizResponse#1001]. Do you need to go to [QuizResponse#1002]?"

After the mark ups "[QuizResponse#" the following 4 numbers is the Quiz ID. Then there is a character "]". I need to get those Quiz ids. There can be 'n' number of such ids. Once I have the ids, I need to replace the response entered for those with the mark ups.

Any help would be appreciated.



May 29, 2009 6:49 PM

Mark Brito said:

Wow, this is useful, all i need now is to tokenize input and try each token.

November 15, 2009 2:30 AM

Yuval said:

great and useful code! thanks a lot!

February 3, 2010 9:00 AM

Chris said:

Nice code, thank you!

Here's a twist, the pattern I'm looking to replace is "anything in brackets, including the brackets". An example would be:

Chris[BLAH] Columbus

Is changed to:

Chris Columbus

I tried

select dbo.PatternReplace('Chris[Blah] Columbus', '[%]','')

but it doesnt seem to work.

Is there a way to get around this?


April 29, 2010 4:12 PM

Adam Machanic said:

Hi Chris,

The problem is that square brackets are used to control the regex used by LIKE, for single characters. So your pattern actually says: Find all matches for the character "%".

To fix this, we have to quote the opening bracket--which can be done using the QUOTENAME function:



... and then you can pass it to the function using the following pattern:

select dbo.PatternReplace('Chris[Blah] Columbus', '[[]%]','')

... there does seem to be a bug here; the space is getting matched and thrown out. I'll take a look and see if I can fix it... I actually thought I already had looked at this previously, but maybe I'm thinking of something else.

April 30, 2010 10:02 AM

Rob said:

Great function with one observation:

-- executing this...

SELECT dbo.PatternReplace(UPPER('6/24/1976 5320305 C101/262'),'[0-9A-Z]','x')

-- produces this (replacing embedded spaces with 'x')...


-- rather than this...

x/xx/xxxx xxxxxxx xxxx/xxx

-- unless I change all your VARCHARs to NVARCHAR


November 8, 2011 12:19 PM

Adam Machanic said:

Hi Rob,

Seems to be the same issue that Chris had. Glad to hear that you've found a workaround. I don't recall if I did any research on it back in April of 2010 but given that I didn't follow up here I'm thinking that I didn't. So now we have the answer. Thanks for sharing!

November 8, 2011 12:57 PM

Iroshan said:

Thanx man...

July 23, 2012 8:37 AM

Joe said:

What if I want to replace all characters up to one special character, in my case underscore "_". So something like "87hdisuh_989", and I want only the remainder after "_"?

August 12, 2012 4:25 AM

Adam Machanic said:


I think you'd want to use the built-in RIGHT function for this, in conjunction with LEN and CHARINDEX:

SELECT RIGHT('87hdisuh_989', LEN('87hdisuh_989') - (CHARINDEX('_', '87hdisuh_989')))

August 12, 2012 1:44 PM

Adam Machanic said:

Another option is to use SUBSTRING, which doesn't require finding the length of the string. It would be interesting to test and see which is faster:

SELECT SUBSTRING('87hdisuh_989', CHARINDEX('_', '87hdisuh_989') + 1, 2147483647)

August 12, 2012 1:46 PM

Joshna said:

Table name: Answers

Column name: Answer

144-TRIAL-Telnet 216

281-TRIAL-ROM 198


121-TRIAL-DRAM 299

257-TRIAL-PRAM 276




till 1722 records

I am suppose to remove [1/2/3digits]-TRIAL-“string”[space][2/3 digits]  using sql statement

“String should be retained as it is”

For example in the row data: 144-TRIAL-Telnet 216,  

I should retain the text Telnet.

Please any help in writing sql statement for retaining only string in the above column

November 21, 2014 7:04 AM

Adam Machanic said:


Substring from character 11 until the first space (found via CHARINDEX).


November 22, 2014 9:46 AM

Joshna said:

Thanks for the help.

Im a beginner learner so please check whether the below statement is correct or not.If not please help me:

select left(SUBSTRING(substring('144-TRIAL-Telnet

216',CHARINDEX('-','144-TRIAL-Telnet 216')+1,100),

CHARINDEX('-',substring('144-TRIAL-Telnet 216',CHARINDEX('-','144-TRIAL-Telnet

216')+1,100))+1,100),CHARINDEX(' ',SUBSTRING(substring('144-TRIAL-Telnet

216',CHARINDEX('-','144-TRIAL-Telnet 216')+1,100),

CHARINDEX('-',substring('144-TRIAL-Telnet 216',CHARINDEX('-','144-TRIAL-Telnet


November 24, 2014 3:33 AM

Adam Machanic said:


Yours returns an empty string. Not sure what's up with that. Here's what I had in mind:


DECLARE @t varchar(50) = '144-TRIAL-Telnet 216'

SELECT SUBSTRING(@t, 11, CHARINDEX(' ', @t) - 11)


November 24, 2014 10:14 AM

Mike said:

I just want to say thank you for this really useful piece of code.

June 17, 2015 3:44 AM

Ken Cummings said:

If the character following the replacement pattern is a space, it replaces that as well as the pattern.  Was this intentional?

September 19, 2017 4:27 PM

Adam Machanic said:


Nope, not intentional - but if you read the prior comments you'll see the issue:

These two things produce the same result:

select patindex ('cd', 'cd')

select patindex ('cd', 'cd ')

Changing the function to use NVARCHAR fixes things:

select patindex (N'cd', N'cd ')


September 20, 2017 10:47 AM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement