Finding pattern and replacing in SQL Server 2008

Question

I have strings that look like:

set @s1 = '#12 + #13 - #14 * 3'

How can I find all the #XXX parts of the string and replace it so that the final string looks like:

hashf('12') + hashf('13') - hash('14') * 3

I tried it using cursors but I took too much time and for performance reasons I don't want to use them.

I tried also regex. The pattern is "#\d+" but how can I apply it in my case?

Always remember [Regular Expressions: Now You Have Two Problems](http://www.codinghorror.com/blog/2008/06/regular-expressions-now-you-have-two-problems.html) and someone that [went mad trying to use them](http://stackoverflow.com/a/1732454/1297603) — Yaroslav, May 13 '13 at 14:06

score 0 · Accepted Answer · answered May 13 '13 at 17:51

I figured out the solution:

DECLARE @s1 VARCHAR(max) 
DECLARE @length INT 
DECLARE @current INT 

SET @s1 = '#12 + #13 - #14 * 3' 
SET @length = Len(@s1) 
SET @current = 0 

DECLARE @returned_value VARCHAR(max) 

WHILE ( @current < @length ) 
  BEGIN 
      SET @current = Charindex('#', @s1, @current) 
      SET @s1 = Stuff(@s1, Charindex('#', @s1, @current) , 1, 'func1(''') 
      SET @s1 = Stuff(@s1, Charindex(' ', @s1, @current) , 1, ''') ') 
      SET @length = Len(@s1) 
      SET @current = Charindex('#', @s1, @current) 

      IF @current = 0 
        BEGIN 
            SET @current = @length 
        END
  END
SELECT @s1

Finding pattern and replacing in SQL Server 2008

1 Answers1