Replace multiple words in a single value using SQL

Question

I have two tables, #customer and #abbr. The #customer table consists of two columns: 'id' and 'name,' while the #abbr table includes entries for abbreviations and their corresponding full forms. My goal is to replace all abbreviations in the customer names with their respective full forms. For instance, if a customer name is 'Object ME CT,' I expect the result to be 'Object Medical Control.' However, with my current query, it yields two rows: 'Object ME Control' and 'Object Medical CT.' I am aiming for a single row per 'id,' where all abbreviations are replaced with their full forms from the #abbr table. How can I achieve this? Thank you!

create table #customer
(
id int identity(1,1)
,cname varchar(100)
)

create table #abbr
(
abbr varchar(100),
fname varchar(100)
)

insert into #customer values('Assign ME CT'),('Assign ME'),('Assign CT')
insert into #abbr values ('ME','Medical'),('CT','Control'),('MMT','Metro')

select *, REPLACE(c.cname, a.abbr,a.fname)
  from #customer c
  join #abbr a
  on c.cname like '%'+a.abbr+'%'
 where id = 1

Answer 1

One way to do this as of SQL 2022, is to split the values in each customer row, replace them, and then zip them back up again. Setting the enable_ordinal flag to 1 in the STRING_SPLIT() function provides the order of the substrings in each string, and using the WITHIN GROUP clause around this ordinal column ensures they are zipped back up in the way they are broken down. Code to do that would be this -

SELECT *
FROM #customer AS c;

SELECT *
FROM #abbr AS a;

SELECT c.id
     , STRING_AGG(IIF(split.value = a.abbr, a.fname, split.value), ' ')WITHIN GROUP(ORDER BY ordinal) AS phrase
FROM #customer AS c
    CROSS APPLY STRING_SPLIT(c.cname, ' ', 1) AS split
    LEFT JOIN #abbr AS a
        ON split.value = a.abbr
GROUP BY c.id;

Produces this -

id          cname
----------- ----------------------
1           Assign ME CT
2           Assign ME
3           Assign CT

abbr        fname
----------- ----------------------
ME          Medical
CT          Control
MMT         Metro

id          phrase
----------- ----------------------
1           Assign Medical Control
2           Assign Medical
3           Assign Control

Alternatively, if on an earlier version of SQL, you could write a custom function to provide the ordinal column. Such a function might look like this -

CREATE FUNCTION dbo.string_split_with_ordinal
(
    @Input VARCHAR(8000)
  , @Delimeter VARCHAR(1)
)
RETURNS @splitValues TABLE
(
    value_order INT IDENTITY(1, 1)
  , value VARCHAR(8000)
)
AS
BEGIN
    DECLARE @tempValue VARCHAR(8000);

    WHILE LEN(@Input) > 0
    BEGIN
        IF CHARINDEX(@Delimeter, @Input) = 0
        BEGIN
            INSERT INTO @splitValues
            VALUES
            (@Input);
            BREAK;
        END;

        SET @tempValue = SUBSTRING(@Input, 1, CHARINDEX(@Delimeter, @Input) - 1);
        SET @Input = SUBSTRING(@Input, LEN(CONCAT(@tempValue, @Delimeter)) + 2, LEN(@Input));

        INSERT INTO @splitValues
        VALUES
        (@tempValue);
    END;

    RETURN;
END;
GO

And using this function in code to produce the desired result would be -

SELECT c.id
     , STRING_AGG(IIF(split.value = a.abbr, a.fname, split.value), ' ')WITHIN GROUP(ORDER BY split.value_order) AS phrase
FROM #customer AS c
    CROSS APPLY dbo.string_split_with_ordinal(c.cname, ' ') AS split
    LEFT JOIN #abbr AS a
        ON split.value = a.abbr
GROUP BY c.id;

Answer 2

I tweaked the query that Caleb provided to ensure we always stitch back the phrase in the order it was split and in order to do that i'm using OPENJSON split trick suggested by Aaron in an article. This worked for me.

SELECT 
    c.id
     , STRING_AGG(IIF(split.value = a.abbr, a.fname, split.value), ' ')WITHIN GROUP(ORDER BY split.[key]) AS phrase
FROM #customer AS c
    CROSS APPLY OPENJSON(N'["' + REPLACE(c.cname, ' ', N'","') + N'"]') AS split
    LEFT JOIN #abbr AS a
        ON split.value = a.abbr
GROUP BY c.id;