November 22, 2011 at 10:12 pm
Hi guys,
I have a string "1|HK$ 500 (Gift voucher)|500|2,1|HK$ 1000 (Gift voucher)|1000|2"
I want to split comma(,) first. So two records will show.
So it will be two separate records like,
1|HK$ 500 (Gift voucher)|500|2
1|HK$ 1000 (Gift voucher)|1000|2
Now I want result like as below i.e. into multiple columns i.e in 4 columns. So it will be,
1 HK$ 500 (Gift voucher) 500 2
1 HK$ 500 (Gift voucher) 1000 2
Any help would be greatly appreciated.
November 23, 2011 at 12:04 am
Guys,
Below one worked for me.
-- Itzik's VATN
CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT TOP (@n) n AS Number FROM Nums WHERE n <= @n ORDER BY n;
GO
-- Erland's split function
CREATE FUNCTION dbo.inline_split_me(@param nvarchar(MAX))
RETURNS TABLE AS
RETURN(
SELECT
ROW_NUMBER() OVER(ORDER BY Number) AS pos,
ltrim(
rtrim(
convert(nvarchar(4000), substring(@param, Number,
charindex(N',' COLLATE Slovenian_BIN2, @param + convert(nvarchar(MAX), N','), Number) - Number)
))) AS Value
FROM dbo.fn_nums(convert(int, len(@param)))
WHERE substring(convert(nvarchar(MAX), N',') + @param, Number, 1) = N',' COLLATE Slovenian_BIN2
)
GO
DECLARE @T TABLE (
id int NOT NULL IDENTITY(1, 1) UNIQUE,
data varchar(max)
);
insert @T values('DEF,KHL,MNO');
insert @T values('DEF,KHL,MNO,LKJ,MNJ,BKS');
SELECT
*
FROM
@T AS T
OUTER APPLY
dbo.inline_split_me(T.data) AS S
PIVOT
(
MAX(Value)
FOR pos IN ([1], [2], [3], [4], [5], [6])
) AS P;
GO
DROP FUNCTION dbo.inline_split_me, dbo.fn_nums;
GO
November 23, 2011 at 1:42 am
Jeff Moden wrote an article about splitting delimited stings here[/url].
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply