How to replace multiple parts of a string with data from multiple rows?

4 votes

5 answers

9310 views

sql-server t-sql sql-server-2016 string-manipulation

                          * Here's a fiddle to show what I'm after .

Given a table with two columns - an integer ID and a text-based string - I want to start with a string value that encodes any number of integers wrapped in curly braces, mixed in with any other valid text characters.

Example: '{1} / {9} ... {12}'

With a single SELECT statement, I want to return a string whereby all the integers (and their wrapping braces) have been replaced with a value derived from my table; specifically, the text value for the row having an ID that matches the number found in the source string.... and any characters outside the curly braces remain untouched. 

Here is an example that fails to complete the task:

    select
      replace('{13} {15}','{'+cast(id as varchar)+'}',isNull(display,''))
    from testing;

This would return 1 row per row in the testing table. For the row with id value = 13, the '{13}' portion of the string is successfully replaced, but the '{15}' portion is not (and vice versa on row 15).

I imagine creating a function that loops through all testing rows and repeatedly attempts replacements would solve the problem. Be that as it may, a straight-up SQL statement would be preferable to looping.

**Example Data**

    +----+-------------------+
    | id |  display          |
    +----+-------------------+
    |  1 |	Apple            |
    |  2 |	Banana           |
    |  3 |	Celery           |
    |  4 |	Dragonfruit      |
    |  5 |	Eggplant         |
    |  6 |	Fenugreek        |
    |  7 |	Gourd            |
    |  8 |	Honeydew         |
    |  9 |  Iceberg Lettuce  |
    | 10 |	Jackfruit        |
    | 11 |	Kale             |
    | 12 |	Lemon            |
    | 13 |	Mandarin         |
    | 14 |	Nectarine        |
    | 15 |	Olive            |
    +----+-------------------+

**Example use cases**

    select replace('{1} {3}',null,null) 
    -- Returns 'Apple Celery'
    
    select replace('{3},{4},{5}',null,null); 
    -- Returns 'Celery,Dragonfruit,Eggplant'
    
    select replace('{1} / {9} ... {12}',null,null); 
    -- Returns 'Apple / Iceberg Lettuce ... Lemon'

Clearly, the replace keyword does not do the job.

PS. If a solution required the format of the string to change in order to facilitate this, that is an option. 

For example: '#1 / #9 ... #12' (to correlate with the earlier example)

In this format, perhaps we could break the string up into a rowset, based on #, take the left characters until we find a non-numeric, join to the testing table based on the numbers taken, replace the # and numbers with the testing table's display value then stuff all those individually modified tokens back into a single string for xml path?

I am using SQL Server 2016 which does not support string_agg. That said, if there is a solution using string_agg, I am still interested in reviewing it.
                        

Asked by youcantryreachingme (1655 rep)

May 2, 2019, 06:42 AM
Last activity: Apr 20, 2023, 07:24 PM

How to replace multiple parts of a string with data from multiple rows?

Related Questions