Hi Ram,
You need to split these values, then concat your suffix/Prefix and again concat the values using comma
Sql server does not have any inbuilt function to split the comma separated values. So you can create a split function in ur database.It will be useful in ur future queries also.Use below code for the same
--------------------Function to split comma delimeted values--------------------------
CREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
GO
-------------------------------Logic to add Prefix ---------------------------
DECLARE @List VARCHAR(8000)
SELECT @List = COALESCE(@List + ',', '') +'R'+ CAST(Item AS VARCHAR)
FROM dbo.SplitString('1,2,2,4', ',')
SELECT @List
---------------------------------------------------------------------------------
Input:
1,2,3,4
-------
OUTPUT :
R1,R2,R2,R4
~ Cheers!!
Thanks!
Anjali Bansal
~Give your best and lead the world