SQL Server 2014 replace with regex -

what best way replace string in column like:

let's have table column type varchar , possible value contains:

'sample text min(my value) continue sample text ) , (' 

note: value can vary.

now replace with:

min(max(my value)) 

so final value in case be:

'sample text min(max(my value)) continue sample text ) , (' 

i perform update on whole table.

is possible using pure t-sql?

so 2 sample rows before => , after transformation:

1. value is: min(10). => value is: min(max(10)) 2. sample min(cat) => sample min(max(cat))  

basically replace occurrences of min(value) min(max(value)) 'value' can string

first of need user defined function search replacing pattern string:

create function dbo.patternreplace (    @inputstring varchar(4000),    @pattern varchar(100),    @replacetext varchar(4000) ) returns varchar(4000) begin    declare @result varchar(4000) set @result = ''    -- first character in match    declare @first int     -- next character start search on     declare @next int set @next = 1     -- length of total string -- 8001 if @inputstring null     declare @len int set @len = coalesce(len(@inputstring), 8001)     -- end of pattern     declare @endpattern int       while (@next <= @len)       begin      set @first = patindex('%' + @pattern + '%', substring(@inputstring, @next, @len))       if coalesce(@first, 0) = 0 --no match - return        begin           set @result = @result +               case --return null, replace, if inputs null                 when  @inputstring null                  or @pattern null                  or @replacetext null null            else substring(@inputstring, @next, @len)         end      break   end   else   begin      -- concatenate characters before match 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            , @len >= (@next + @endpattern - 1)         set @endpattern = @endpattern + 1       --either @ end of pattern or @next + @endpattern = @len      set @result = @result + @replacetext      set @next = @next + @endpattern - 1   end       end       return(@result)    end 

read more here

after creating function can try this:

declare @x varchar(max) set @x = 'sample text min(my value) continue sample text ) , ('  declare @val varchar(max)  set @val =  substring(substring(@x,charindex('min(',@x)+4,len(@x)-charindex('min(',@x)),1,charindex(')',@x)-(charindex('min(',@x)+4))  select replace(dbo.patternreplace(@x,'%min(','min(max('),'min(max('+@val+')','min(max('+@val+'))') 

and can see output is:

occurrence of min(xxx) more once:

finally can update table below:

update  yourtable set     yourcolumn = replace(dbo.patternreplace(yourcolumn, '%min(', 'min(max('),                    'min(max(' + substring(substring(yourcolumn,charindex('min(', yourcolumn)+ 4,len(yourcolumn)- charindex('min(',yourcolumn)), 1,charindex(')', yourcolumn)- ( charindex('min(', yourcolumn) + 4 ))+ ')',                    'min(max(' + substring(substring(yourcolumn,charindex('min(', yourcolumn)+ 4,len(yourcolumn)- charindex('min(',yourcolumn)), 1,charindex(')', yourcolumn)- ( charindex('min(', yourcolumn) + 4 ))+ '))');