0

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
2
  • What version of SQL Server are you on?
    – Caleb Carl
    Nov 13, 2023 at 23:55
  • I'm using SQL server 2019 Nov 13, 2023 at 23:59

2 Answers 2

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;
9
  • 1
    Thanks, Caleb! A question here - how does this code ensure that the items are joined back in the same order they were broken down? For example, how does the code ensure that Assign ME CT is returned as Assign Medical Control and not Control Medical Assign or any other combination? Nov 14, 2023 at 0:45
  • @lifeisajourney Good question, and IIRC you can't.
    – J.D.
    Nov 14, 2023 at 0:53
  • @J.D. Is there a way to add an additional column that will keep track of how the word is being broken down and then use that to order by when joining back? Nov 14, 2023 at 0:55
  • ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY (SELECT NULL)) AS word_order Nov 14, 2023 at 1:04
  • 1
    Not using the STRING_AGG function until SQL 2022, I don't believe. With a custom function that iterates through each string in a while loop, you can. In SQL 2022, there is an enable_ordinal flag that will provide this exact thing. I could write up a way for you to have this, if you'd like. Order by (select null) specifically omits a specific order if I remember right.
    – Caleb Carl
    Nov 14, 2023 at 1:07
0

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;
4
  • 1
    That is a clever solution to get the ordinal value of each substring within the string. I like that better than a function with a while loop, personally.
    – Caleb Carl
    Nov 14, 2023 at 2:13
  • 1
    One thing I did notice that you will need to be careful of, is that the [value] column of the OPENJSON function is NVARCHAR, and the [abbr] column in the #abbr table is VARCHAR. Joining on those columns while they are different creates a huge implicit conversion performance hit that you will need to address for this to scale. stackoverflow.com/questions/28934128/…
    – Caleb Carl
    Nov 14, 2023 at 2:22
  • 1
    Also, in your WITHIN GROUP clause, you only need to specify ORDER BY split.[key] because the entire query is already grouped by string id.
    – Caleb Carl
    Nov 14, 2023 at 2:24
  • Agreed! I changed the order by to only include split.[key]. Regarding the implicit conversion, I intend on using it within a view for reporting so a little bit of performance penalty wouldn't hurt much. Thanks for all your help, Caleb! Really appreciate it! Nov 14, 2023 at 2:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.