Replacing values in a string from another table without nested REPLACE

user_10864093 0
Viewed 272

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
tsql sql
1 Answers

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;
user_67487580
Related