Replace delimited values in a column with values from another table

Question

I have a table CountryCodes with 2 columns (code, description) an example is below:

code description
AD   Andorra
AE   United Arab Emirates
AF   Afghanistan

I have a column called Markets in a view that contains strings as below:

Markets (this is one column)
AD | AE | AF
US | UK
NZ | AU | AD

I need to write a select statement that will lookup the codes from the Market column in the CountryCodes table between the delimiter (|). For example:

AD | AE | AF ----> Andorra | United Arab Emirates | Afghanistan
US | UK ----> United States | United Kingdom

I know this could be attached by wrapping the select in a tonne of replace statements, but I have 249 codes in this table and that seems horribly inefficient to write and maintain.

I have also looked into the string_split function, but it is not supported on my version of SQL server which is: Microsoft SQL Azure (RTM) - 12.0.2000.8

Does anyone have any suggestions?

Storing them as arrays (as opposed to storing each element on their own rows in another table) is only good if there is no need for elements to be join'd onto later (think description lookups). Example of which is tags data. However, SQL Server does not support arrays, and there is no specialized syntax/functionality to filter on delimited string, the filter performance will likely be abysmal. So yeah, store each of those country-markets association on their own rows http://shon.github.io/2015/12/21/postgres_array_performance.html — Michael Buen, Apr 29 '19 at 07:23
@MichaelBuen True, SQL Server does not support array types but it does include built in support for XML and JSON which can be used to store arrays. That being said, without writing elaborate check constraints on the xml/json data, it's impossible to ensure that the data is, in fact, correct. A normalized structure (row for each array element) is much easier to protect against invalid data, and is much easier to work with. (That being said, I know nothing about PostgreSQL, I'm only talking about SQL Server). — Zohar Peled, Apr 29 '19 at 07:48

score 0 · Answer 1 · answered Apr 29 '19 at 04:50

As the comment by @Jens correctly points out, storing your country codes as pipe-delimited strings, of varying length, is bad table design. Instead, it would be much better to store one relationship per record, something like this:

ID | code
1  | AD
1  | AE
1  | AF
2  | US
2  | UK
3  | NZ
3  | AU
3  | AD

Then, if you wanted to convert this to CSV lists of markets for each ID, you could simply try:

SELECT
    m.ID,
    STRING_AGG(cc.description, ',') WITHIN GROUP (ORDER BY m.ID) AS markets
FROM Markets m
INNER JOIN CountryCodes cc
    ON m.code = cc.code
GROUP BY
    m.ID;

Zohar Peled · Accepted Answer · 2019-04-29T06:56:47.677

Tim's answer is very good.
You should normalize your database. That is the correct way to solve this problem.
For more information, read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!

However there are plenty of times when you simply can't change the database structure for any number of reasons. Sometimes, the change is too expensive, sometimes you are working with a 3rd party database.
Whatever the reason is, I've had answered plenty of questions here (and in other places as well) where the database structure should be changed but it's not an option.

Therefor, I'll give you an answer that shows how you can get your desired output without changing the database structure.

First, create and populate sample tables (Please save us this step in your future questions):

DECLARE @Codes AS TABLE
(
    Code char(2),
    Description varchar(100)
);

INSERT INTO @Codes (Code, Description) VALUES
('AD', 'Andorra'),
('AE', 'United Arab Emirates'),
('AF', 'Afghanistan'),
('UK', 'United Kingdom');

DECLARE @T AS TABLE
(
    Markets varchar(100)
);

INSERT INTO @T (Markets) VALUES
('AD | AE | AF'),
('US | UK'),
('NZ | AU | AD');

Then, I'm using a common table expression to split the values in the Markets column into rows.
The Charindex is there to preserve the original order of the values in the result. (Note: this trick only works if the values are unique within each row). Note: String_split is supported by Azure database but require compatibility level of at least 130

WITH CTE AS
(
SELECT Markets, 
       TRIM(Value) As Code, 
       CHARINDEX(Value, Markets) As Sort
FROM @T
CROSS APPLY STRING_SPLIT(Markets, '|')
)

Then, using string_agg I'm reconstructing the rows, but this time with their translations.
string_agg is supported by Azure database but require compatibility level of at least 140.

Note: The left join and isnull are to handle cases where there is a value that can't be found in the codes table. In your actual case you might want to discard these values - if that is the case, change the left join to an inner join and remove the isnull.

SELECT Markets, 
       STRING_AGG(ISNULL(Description, 'N/A'), ' | ') WITHIN GROUP(ORDER BY Sort) As Translated
FROM CTE
LEFT JOIN @Codes C
    ON CTE.Code = C.Code
GROUP BY Markets

Results:

Markets         Translated
AD | AE | AF    Andorra | United Arab Emirates | Afghanistan
NZ | AU | AD    N/A | N/A | Andorra
US | UK         N/A | United Kingdom

You can see a live demo on db<>fiddle

If your compatibility level is less than 140 you can use the older trick for string aggregation using for xml.

If your compatibility level is less than 130 you can use a user defined function to split the string.

I just pointed to this, as the question mentions *12.0.2000.8*... And the OP explicitly states, that `STRING_SPLIT()` is not supported... — Shnugo, Apr 29 '19 at 06:56
@Shnugo yes, but it's an azure version, and according to [official documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017), all azure products supports compatibility level up to 150. — Zohar Peled, Apr 29 '19 at 07:01
"Please save us this step in your future questions" Some popular fiddles have Text to DDL functionality, e.g., http://sqlfiddle.com/ https://www.db-fiddle.com/ It would be nice if https://dbfiddle.uk/ supported it too, or if the OP do it by themselves — Michael Buen, Apr 29 '19 at 07:37
@MichaelBuen I find that most of the time, when I write this in my answers (and I do that a lot), and the answer gets accepted, the OP have learned how easy it is to provide sample data and actually will do that in their next questions. I like to think of it as my small contribution to the overall quality of sql related questions on Stackoverflow. — Zohar Peled, Apr 29 '19 at 07:44
@ZoharPeled Thank you very much, this helped me solved my problem. Turns out the string_split function is supported on my DB, I was just using it incorrectly.. I still have a lot to learn! I will make sure to provide some sample data in my future questions too - it really just didn't occur to me to do so! Thanks again — Matthew Jackson, May 01 '19 at 04:39

Replace delimited values in a column with values from another table

2 Answers2