im working in sql server 2008, i want to concatenate two strings, and the condition is
@str1 = 'A1,B1,C1'
@str2 = 'A2,B2,C2'
i want the result as
@result = 'A1,A2,B1,B2,C1,C2'
Please help...
4 Answers
The way you are storing your data is really bad practice. However here is a solution for training purposes:
DECLARE
@str1 varchar(30) = 'A1,B1,C1',
@str2 varchar(30) = 'A2,B2,C2',
@result varchar(60)
;WITH split as
(
SELECT t.c.value('.', 'VARCHAR(2000)') x
FROM (
SELECT x = CAST('<t>' +
REPLACE(@str1 + ',' + @str2, ',', '</t><t>') + '</t>' AS XML)
) a
CROSS APPLY x.nodes('/t') t(c)
)
SELECT
@result =
STUFF((
SELECT ',' + x
FROM split
ORDER BY x
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '')
SELECT @result
Result:
A1,A2,B1,B2,C1,C2
Comments
First create a split function to get you items seperately:
CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
GO
Now you can first split then and order your result and then concat the values
DECLARE @DelimitedString NVARCHAR(128)
SET @DelimitedString = @str1 + ',' + @str2
SELECT @result = COALESCE(@result + ',', '') +Data
FROM (SELECT Data
FROM dbo.Split(@DelimitedString, ',')
ORDER BY Data)
Comments
SET @result = @str1 + ',' + @str2
SELECT @result
UPDATE 1
I think the best approach will be to, first get full string and then splitting it with comma, and then storing that in a temp table and then sorting, might be a good idea.
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
SELECT Item FROM
dbo.SplitString(@result,',') ORDER BY Item
1 Comment
user2695128
i want exactly with this format 'A1,A2,B1,B2,C1,C2'
Try:
SELECT stuff((SELECT ',' + T.str FROM
(SELECT
PARSENAME(REPLACE(@str1,',','.'),1) str UNION ALL
PARSENAME(REPLACE(@str1,',','.'),2) str UNION ALL
PARSENAME(REPLACE(@str1,',','.'),3) str UNION ALL
PARSENAME(REPLACE(@str2,',','.'),1) str UNION ALL
PARSENAME(REPLACE(@str2,',','.'),2) str UNION ALL
PARSENAME(REPLACE(@str2,',','.'),3) str )T
ORDER BY T.str
FOR XML PATH('')),1,1,'')
1 Comment
Haytem BrB
@user2695128: welcome, if it is working for you, you can please mark it as an answer
set @result = @str1 + ',' + @str2