sql stored procedure convert code to text in string with a raw of codes

Question

I have a string with codes that seperated with a commas,

example:

@inputText = "1,2,3,4,5"

and i need a convert to:

@outputText = "apple, peach, orange, banana, onion"

i have codes table:

id name
-- ----
1  apple
2  peach
3  orange
4  banana
5  onion

i am using sql server 2008

i need the stored procedure code that doing it, thanks.

What database are you using? MySQL, SQL*Server, Postgresql? What have you tried so far? — Raad, Mar 05 '13 at 10:47

score 4 · Accepted Answer · edited May 23 '17 at 12:03

You can use FOR XML PATH along with LIKE to replace the values in the comma separated list:

declare @text varchar(50) = '1,2,3,4,5'

;with cte as 
(
    select @text list
) 
select 
(
    select ', '+c.name
    from codes c
    where ','+t1.list+',' like '%,'+cast(c.ID as varchar(10))+',%'
    for xml path(''), type
).value('substring(text()[1], 3)', 'varchar(max)') as NewList
from cte t1

This is based on an answer by @Mikael Eriksson

sql stored procedure convert code to text in string with a raw of codes

1 Answers1