Multiple split - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Multiple split

Author

Topic

PrathibhaM
Starting Member

1 Post

Posted - 2013-09-18 : 07:06:33

I have string of format with two delimiters ‘|’ pipe and ‘,’ comma
'1,100,12345|2,345,433|3,23423,123|4,33,55'
And have to insert into table columns as below
seq invoice amount
1 100 12345
2 345 433
3 23423 123
4 33 55

Please help

Thanks & Regards,
Prathibha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 08:04:36

[code]CREATE FUNCTION dbo.fnTokenizeContent
(
@Content VARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN (
SELECT r.n.value('(c[1])', 'INT') AS Seq,
r.n.value('(c[2])', 'INT') AS Invoice,
r.n.value('(c[3])', 'INT') AS Amount
FROM (
VALUES (CAST('<r><c>' + REPLACE(REPLACE(@Content, '|', '</c></r><r><c>'), ',', '</c><c>') + '</c></r>' AS XML))
) AS d(Data)
CROSS APPLY d.Data.nodes('(r)') AS r(n)
)
GO
DECLARE @Data VARCHAR(100) = '1,100,12345|2,345,433|3,23423,123|4,33,55';

SELECT *
FROM dbo.fnTokenizeContent(@Data);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-18 : 08:37:46

This Query Gives Wt U Need :

SELECT STUFF((SELECT '|'+CAST(seq as VARCHAR)+','+CAST(invoice as VARCHAR)+','+CAST(amount as VARCHAR )
FROM (SELECT seq,invoice,amount FROM tablename)x
ORDER BY seq,invoice,amount
FOR XML PATH('')),1,1,'')

veeranjaneyulu

VasiAnu
Starting Member

9 Posts

Posted - 2013-09-24 : 07:47:21

DECLARE @String VARCHAR(100)='1,100,12345|2,345,433|3,23423,123|4,33,55'
DECLARE @myXML AS XML = '<H><r>' + REPLACE(@String, '|', '</r><r>') + '</r></H>'
;WITH CTE
AS
(
SELECT CAST(N'<H><r>' + REPLACE(REPLACE(vals.id.value('.', 'VARCHAR(50)'),',','|'), '|', '</r><r>') + '</r></H>' AS XML) AS val
FROM @myXML.nodes('/H/r') AS vals(id)
)
--SELECT * FROM CTE
SELECT DISTINCT S.a.value('(/H/r)[1]', 'VARCHAR(50)') AS Seq,
S.a.value('(/H/r)[2]', 'VARCHAR(50)') AS Invoice,
S.a.value('(/H/r)[3]', 'VARCHAR(50)') AS Amount
FROM CTE CROSS APPLY val.nodes('/H/r') S(a)
ORDER BY Seq

Thanks,
VA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources