Replacing values in a string from another table without nested REPLACE

Question

In one table, I have long strings which contain a series of tokens that need to be replaced from values in a number of columns in a separate customer table. There are around 30 tokens but below is a representation of it. So for example, the FName value in Table2 needs to replace the [fname] token in the string in Table1 and so on.

I can do it as below with a series of nested REPLACE statements, but it's ugly and unruly given the number of tokens that need to be replaced. Any suggestions would be much appreciated?

Notes: I'm doing this in T-SQL. And the token name doesn't always match the column name 100%.

CREATE TABLE #Table1 (StringtoReplace NVARCHAR(200))
CREATE TABLE #Table2 (Greeting NVARCHAR(30), FName NVARCHAR(30), LName NVARCHAR(30))

INSERT INTO #Table1 VALUES ('[greeting] [fname] [lname] Here is the message')

INSERT INTO #Table2 
VALUES ('Hello','John','Smith'),
       ('Hi','Fred','Jones'),
       ('Howdy','Sue','Brown')

SELECT REPLACE(REPLACE(REPLACE(t1.StringToReplace,'[greeting]',t2.Greeting),'[fname]',t2.fname),'[lname]',t2.lname) AS UpdatedString
FROM #Table1 t1
CROSS APPLY #Table2 t2

Here's the required output.

UpdatedString
-------------------------------------------
Hello John Smith Here is the message
Hi Fred Jones Here is the message
Howdy Sue Brown Here is the message

Side note: APPLY doesn't seem to be necessary here. A CROSS JOIN should be enough. — sticky bit
– sticky bit, Commented Feb 2, 2021 at 21:07
In my actual scenario, I'm not using CROSS APPLY or CROSS JOIN. This was just for quick illustration here. Thanks though. — WAMLeslie
– WAMLeslie, Commented Feb 2, 2021 at 21:13

SteveC · Accepted Answer · 2021-02-03 03:26:07Z

1

One alternative would be successively CROSS APPLY the replacements. Is this better? Idk. It performs the calculation in the FROM clause and cleans up the SELECT list. It returns the correct output as well

drop table if exists #Table1;
go
CREATE TABLE #Table1 (StringtoReplace NVARCHAR(200));

drop table if exists #Table2;
go
CREATE TABLE #Table2 (Greeting NVARCHAR(30), FName NVARCHAR(30), LName NVARCHAR(30));

INSERT INTO #Table1 VALUES ('[greeting] [fname] [lname] Here is the message');

INSERT INTO #Table2 
VALUES ('Hello','John','Smith'),
       ('Hi','Fred','Jones'),
       ('Howdy','Sue','Brown');

SELECT v3.StringtoReplace UpdatedString
FROM #Table1 t1
     cross apply #Table2 t2
     cross apply (values (replace(t1.StringToReplace, '[greeting]', t2.Greeting))) v1(StringtoReplace)
     cross apply (values (replace(v1.StringToReplace, '[fname]', [fname]))) v2(StringtoReplace)
     cross apply (values (replace(v2.StringToReplace, '[lname]', [lname]))) v3(StringtoReplace);

UpdatedString
Hello John Smith Here is the message
Hi Fred Jones Here is the message
Howdy Sue Brown Here is the message

If instead of temp tables the rows were stored in physical tables, then you could create a reusable inline table valued function, itvf. Using the itvf the query is only 3 lines of code and it produces the same output as before.

drop table if exists dbo.test_Table1;
go
CREATE TABLE dbo.test_Table1 (StringtoReplace NVARCHAR(200));

drop table if exists dbo.test_Table2;
go
CREATE TABLE dbo.test_Table2 (Greeting NVARCHAR(30), FName NVARCHAR(30), LName NVARCHAR(30));

INSERT INTO dbo.test_Table1 VALUES ('[greeting] [fname] [lname] Here is the message');

INSERT INTO dbo.test_Table2 
VALUES ('Hello','John','Smith'),
       ('Hi','Fred','Jones'),
       ('Howdy','Sue','Brown');

drop function if exists dbo.test_fnReplace;
go
create function dbo.test_fnReplace(
  @StringtoReplace              nvarchar(200))
returns table as return
select v3.StringtoReplace UpdatedString
from dbo.test_Table2 t2
     cross apply (values (replace(@StringtoReplace, '[greeting]', t2.Greeting))) v1(StringtoReplace)
     cross apply (values (replace(v1.StringToReplace, '[fname]', [fname]))) v2(StringtoReplace)
     cross apply (values (replace(v2.StringToReplace, '[lname]', [lname]))) v3(StringtoReplace);
go

select fnRep.UpdatedString
from dbo.test_Table1 t1
     cross apply dbo.test_fnReplace(t1.StringtoReplace) fnRep;

edited Feb 3, 2021 at 3:26

answered Feb 2, 2021 at 21:41

SteveC

6,0252 gold badges14 silver badges26 bronze badges

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

6 Comments

WAMLeslie Over a year ago

Yes, thanks. That works too. Although this would still require 30 replace statements, just not nested the same way.

SteveC Over a year ago

The pattern could be made to be dynamic but then 30 is not so many if you only have to do it once.

SteveC Over a year ago

I would think there would be fewer keystrokes to do it manually with this method versus nested REPLACE

WAMLeslie Over a year ago

True enough. 30 isn't terrible. I will have to do this at a few places though, so I was hoping it could be a bit more dynamic so I could reuse it. I do like your option though since it's a more visually appealing way of managing the nested replaces.

SteveC Over a year ago

You've provided temp tables here. To create a table valued function which references the 'table2' table would require making it a physical table. It's the same columns always? A physical table to store 'table2' data is ok?

|

Collectives™ on Stack Overflow

Replacing values in a string from another table without nested REPLACE

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related