SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Multiple split
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PrathibhaM
Starting Member

1 Posts

Posted - 09/18/2013 :  07:06:33  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 09/18/2013 :  08:04:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
159 Posts

Posted - 09/18/2013 :  08:37:46  Show Profile  Reply with Quote
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

Edited by - VeeranjaneyuluAnnapureddy on 09/18/2013 08:41:44
Go to Top of Page

VasiAnu
Starting Member

India
9 Posts

Posted - 09/24/2013 :  07:47:21  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000