CODEWITHSUNDEEP
sqlsql-servert-sql
Farhad-Taran
Farhad-Taran

Reputation: 6512

how to split and concatenate in sql server?

I am passing in a string into a stored procedure to be used in a select statement using dynamic sql:

@groups as nvarchar(1000) = 'group1,group10,group8'

I might just pass in string of numbers, eg, '1,2,3,4'

I want to split these values and then concatenate them so that they end up in the following manner :

'rmc.group1,rmc.group10,rmc.group8'

Upvotes: 3

Views: 1538

Answers (3)

Amit Singh
Amit Singh

Reputation: 8109

Sql Fiddle Demo 

Select Replace('group1,group10,group8','group','rmc.group')

Upvotes: 4

t-clausen.dk
t-clausen.dk

Reputation: 44326

declare @groups nvarchar(1000) ='group1,group10,group8'

set @groups = 'rmc.' + replace(@groups, ',', ',rmc.')
select @groups

Result:

rmc.group1,rmc.group10,rmc.group8

Upvotes: 4

Devart
Devart

Reputation: 121932

Try this one -

DECLARE @groups nvarchar(1000) = 'group1,group10,group8'

SELECT STUFF((
    SELECT ',rmc.' + t
    FROM (
          SELECT t = t.c.value('.', 'VARCHAR(50)')
          FROM (
               SELECT ID = CAST ('<t>' + REPLACE(@groups, ',', '</t><t>') + '</t>' AS XML)
          ) r 
          CROSS APPLY ID.nodes ('/t') t(c)
    ) t
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1,1, '')

Output -

------------------------------------
rmc.group1,rmc.group10,rmc.group8

Upvotes: 2

Related Questions