SQL Server 2014 replace with regex

Question

What is the best way to replace string in column like:

Let's say I have table with column type varchar and with the possible value that contains:

'sample text min(my value) continue with sample text with ) and ('

Note: my value can vary.

Now I would like to replace it with:

min(max(my value))

so that the final value would in that case be:

'sample text min(max(my value)) continue with sample text with ) and ('

I would like to perform update on the whole table.

Is it possible using pure T-SQL?

So two sample rows before => and after transformation:

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

Basically replace all occurrences of min(value) with min(max(value)) where 'value' can be any string

Show sample values with what you want to get.

Gordon Linoff
– Gordon Linoff

2016-07-04 14:23:14 +00:00
Commented Jul 4, 2016 at 14:23 — Gordon Linoff
– Gordon Linoff, Commented Jul 4, 2016 at 14:23
added in description

Snorlax
– Snorlax

2016-07-04 14:28:36 +00:00
Commented Jul 4, 2016 at 14:28 — Snorlax
– Snorlax, Commented Jul 4, 2016 at 14:28

Community · Accepted Answer · 2018-11-13 23:03:11Z

First of all you need this user defined function to search for replacing a pattern with string:

CREATE FUNCTION dbo.PatternReplace
(
   @InputString VARCHAR(4000),
   @Pattern VARCHAR(100),
   @ReplaceText VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
   DECLARE @Result VARCHAR(4000) SET @Result = ''
   -- First character in a match
   DECLARE @First INT
    -- 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
    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, just like REPLACE, if inputs are NULL
                WHEN  @InputString IS NULL
                 OR @Pattern IS NULL
                 OR @ReplaceText IS NULL THEN NULL
           ELSE SUBSTRING(@InputString, @Next, @Len)
        END
     BREAK
  END
  ELSE
  BEGIN
     -- 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
  END
      END
      RETURN(@Result)
   END

Read more here

After creating this function you can try this:

DECLARE @x VARCHAR(max)
SET @x = 'sample text min(my value) continue with sample text with ) and ('

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 you can see that the output is:

Occurrence of min(xxx) more than once:

Finally you can simply update your table as 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 ))+ '))');

and what if there is more then one occurence of min(*) in the column value of given row?
@kulawyjoe I have updated my answer and include the Update statement too. For occurrence more that once, the above solution should work fine too.

vercelli · Accepted Answer · 2016-07-04 14:38:07Z

1

If you are using a variable like @myvalue

UPDATE TABLE
   SET COLUMN = REPLACE(COLUMN, 'min('+@myvalue+')', 'min(max('+@myvalue+'))')

If it's a column on the same table you want to update:

UPDATE TABLE
   SET COLUMN = REPLACE(COLUMN, 'min('+TABLE.myValue+')', 'min(max('+TABLE.myValue+'))')

answered Jul 4, 2016 at 14:38

vercelli

4,7772 gold badges16 silver badges15 bronze badges

3 Comments

Snorlax Over a year ago

myvalue can be different in each row, also in one row there may be few occurences of myvalue with different values so it will not work

vercelli Over a year ago

@kulawyjoe - it wasn't clear value could have many values per row

Snorlax Over a year ago

I think it was clear as these are two sample rows from the question with old and new values after => 1. the value is: min(10). => the value is: min(max(10)) 2. sample min(cat) => sample min(max(cat))

Tim Biegeleisen · Accepted Answer · 2016-07-04 14:51:46Z

1

SQL Server does not have very good regex support out of the box. But you can still achieve what you want using a complex series of string manipulations.

SELECT STUFF(STUFF(col, CHARINDEX('min(', col), 4, 'min(max('),
             CHARINDEX(')', col, CHARINDEX('min(', col)),
             1,
             '))')
FROM yourTable

edited Jul 4, 2016 at 14:51

answered Jul 4, 2016 at 14:23

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

6 Comments

Snorlax Over a year ago

my value can have different values in each row

Tim Biegeleisen Over a year ago

@kulawyjoe I'm sorry, but your question is not very clear. Please add a sample table to your question, and the expected output also as a sample table.

Snorlax Over a year ago

I added it in the question. Basicaly replace all occurences of min(value) with min(max(value)) where 'value' can be any string

Tim Biegeleisen Over a year ago

The answer given by @vercelli might be along the lines of what you want.

Snorlax Over a year ago

not really :/ the string inside min() may be different for each row

|

Collectives™ on Stack Overflow

SQL Server 2014 replace with regex

3 Answers 3

3 Comments

3 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related