T-SQL replace two values in a string simultaneously

Question

I have a chunk of text string @originalCommand within which I want to replace values $tablelist and @dms:

Currently I managed to get below working but some lines are repetitive actions.

SET @originalCommand    = '$tablelist='table_1', $dms='dms_1''

PRINT '*** Original command text:';
PRINT @originalCommand;

--replace variables' values in ps script
SET @replaceStart       = PATINDEX('%$tablelist=''%', @originalCommand) + LEN('$tablelist=''');
SET @replaceLength      = PATINDEX('%''%', SUBSTRING(@originalCommand, @replaceStart, LEN(@originalCommand)));
IF @tableList = ''
    SET @replaceString = CONCAT(@schemaName, '.', @tableName)
ELSE
    SET @replaceString = @tableList;

SET @newCommand         = REPLACE(@originalCommand, SUBSTRING(@originalCommand, @replaceStart, @replaceLength - 1), @replaceString);


SET @replaceStart       = PATINDEX('%$dms=''%', @newCommand) + LEN('$dms=''');
SET @replaceLength      = PATINDEX('%''%', SUBSTRING(@newCommand, @replaceStart, LEN(@newCommand)));
SET @replaceString = @dms;
SET @newCommand         = REPLACE(@newCommand, SUBSTRING(@newCommand, @replaceStart, @replaceLength - 1), @replaceString);

PRINT '';
PRINT '*** New command text:';
PRINT @newCommand;

Expected result is working but I'm trying to achieve it without repeating the lines of replace code:

SET @dms = 'dms_new';
SET @tableName      = 'table_new';
'$tablelist='table_new', $dms='dms_new''

Why does this have to be done in T-SQL? It's string processing is notoriously weak, and you surely have access to other languages or tools that this would be far more straightforward in. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 13, 2021 at 10:33

Bill Jetzer · Accepted Answer · 2021-10-13 12:34:30Z

1

You can take advantage of a peculiarity of SQL server that allows you to make multiple changes to a variable when setting its value in a select statement involving multiple rows.

Using this method you can make as many substitutions as you want, by adding entries to the marker "values table".

The tokens in the original command text can appear in any order, but each token can appear only once.

declare @sqlCommand nvarchar(100) = '$tablelist=''table_1'', $dms=''dms_1'''
       ,@tableList  nvarchar(100) = 'table_new'
       ,@schemaName nvarchar(100) = 'fu'
       ,@tableName  nvarchar(100) = 'bar'
       ,@dms        nvarchar(100) = 'dms_new'
;

-- Compile a table holding all the info necessary to make the substitutions
declare @swap table (pos int, numChars int, newVal nvarchar(100));

insert into @swap
    select pos.beg,pos.numChars,marker.newVal
    from (values
         ('$tablelist=''',coalesce(nullif(@tableList,''),concat(@schemaName,'.',@tableName)))
        ,(      '$dms=''',@dms)
        -- add more rows here if necessary
         ) marker(token,newVal)
         cross apply (
        select pos.beg
              ,charindex('''',@sqlCommand,pos.beg+1) - pos.beg
        from (values (charindex(marker.token,@sqlCommand) + len(marker.token))) pos(beg)
         ) pos(beg,numChars)
;

/*
   Here's where the magic happens.
   The variable will be updated once for each row in the table.
   Note that we do them from the back of the string to the front,
   so that the string positions remain valid after each substitution.

   And use stuff() rather than replace() to guarantee
   that we make exactly the substitutions we want and no more.
*/
select @sqlCommand = stuff(@sqlCommand,pos,numChars,newVal)
from @swap
order by pos desc
;

print @sqlCommand;

answered Oct 13, 2021 at 12:34

Bill Jetzer

1,1356 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charlieface Over a year ago

You really really shouldn't use that aggregation method, it's undefined behaviour with multiple rows, see dba.stackexchange.com/a/132709/220697. Instead use STRING_AGG or FOR XML

Bill Jetzer Over a year ago

@Charlieface: I've only ever used string_agg and for xml to concatenate strings. Can you illustrate how to use either function to make multiple substitutions within a string?

Collectives™ on Stack Overflow

T-SQL replace two values in a string simultaneously

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related