I’m looking for a way to parse a semicolon delimited list of values, join on another table using the values and return values from that other table and re-concatenate into a new field.
Here are the examples:
Table A:
Here is the table with the PFD values:
Table B:
Here is the results I’d like:
Table C:
Is this possible? Thank you!
Advertisement
Answer
Assuming you are using at least SQL Server 2016, you can use a combination of string_split
and for xml path
as follows:
select * from tableA cross apply ( select stuff(( select concat(';', b.alias) from String_Split(device_platform_id,';') join tableB b on b.name=value for xml path ('') ),1,1, '') Device_Platform_Alias )d
If you happen to be using a version prior to 2016 you can use this alternative method that uses xml to split the value on ‘;’ instead of the string_split
function:
select * from tableA cross apply ( select stuff(( select concat(';', b.alias) from ( select value = y.i.value('(./text())[1]', 'varchar(max)') from ( select x = convert(xml, '<i>' + replace(device_platform_id, ';', '</i><i>') + '</i>').query('.') ) as a cross apply x.nodes('i') as y(i) )v join tableB b on b.name=value for xml path ('') ),1,1, '') Device_Platform_Alias )d