tag:blogger.com,1999:blog-8585649008635531444.post7404830861823573314..comments2024-03-21T18:00:19.162+05:30Comments on SQL Master: How To Split A Comma Delimited StringAnonymoushttp://www.blogger.com/profile/13041367988205995767noreply@blogger.comBlogger22125tag:blogger.com,1999:blog-8585649008635531444.post-37104627139960927252015-12-31T01:35:15.116+05:302015-12-31T01:35:15.116+05:30Hi,
What does the "M" in the function s...Hi,<br /><br />What does the "M" in the function stand for?Anonymoushttps://www.blogger.com/profile/12612854379944608646noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-57602565386416794852013-03-15T15:35:03.911+05:302013-03-15T15:35:03.911+05:30Hi,
This is a solution I've been looking for....Hi,<br /><br />This is a solution I've been looking for. May you please tell me how it worksAnonymoushttps://www.blogger.com/profile/09372139448476148995noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-899732606664014902013-03-15T15:34:03.111+05:302013-03-15T15:34:03.111+05:30Hi,
This is a great solution. May you please expl...Hi,<br /><br />This is a great solution. May you please explain it so that I can understand how it works.<br /><br />Thanks<br />ThatoAnonymoushttps://www.blogger.com/profile/09372139448476148995noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-2806793980537548922013-03-13T11:43:39.494+05:302013-03-13T11:43:39.494+05:30Hi Akshamya,
Sorry didn't get your question. I...Hi Akshamya,<br />Sorry didn't get your question. If you want insert values a table you can do like this:<br /><br />;WITH Cte AS<br /><br />(<br />SELECT<br />id,<br />CAST('' + REPLACE( Allnames, ',' , '') + '' AS XML) AS Names<br />FROM Test<br />)<br />INSERT INTO AnotherTable(id, names)<br />SELECT<br />ID,<br />Split.a.value('.', 'VARCHAR(100)') AS Names<br />FROM Cte<br />CROSS APPLY Names.nodes('/M') Split(a)Anonymoushttps://www.blogger.com/profile/13041367988205995767noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-9786504179761011642013-03-13T11:43:08.732+05:302013-03-13T11:43:08.732+05:30Hi Akshamya,
Sorry didn't get your question. ...Hi Akshamya,<br />Sorry didn't get your question. If you want insert values a table you can do like this:<br /><br />;WITH Cte AS<br /><br />(<br /> SELECT<br /> id,<br /> CAST('' + REPLACE( Allnames, ',' , '') + '' AS XML) AS Names<br /> FROM Test<br />)<br />INSERT INTO AnotherTable(id, names)<br />SELECT<br /> ID,<br /> Split.a.value('.', 'VARCHAR(100)') AS Names<br />FROM Cte<br />CROSS APPLY Names.nodes('/M') Split(a)Anonymoushttps://www.blogger.com/profile/13041367988205995767noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-88804748933551295072013-03-13T11:33:49.102+05:302013-03-13T11:33:49.102+05:30How can i insert these split values to diffrent ta...How can i insert these split values to diffrent tableAnonymoushttps://www.blogger.com/profile/02561305523734867675noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-55061065579291006362013-02-08T22:08:17.280+05:302013-02-08T22:08:17.280+05:30I have no language for say thanks to you. Great ma...I have no language for say thanks to you. Great man.<br /><br />It was very helpful for me many many thanks Anonymoushttps://www.blogger.com/profile/12690466403155844755noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-60846934785051883652013-02-04T22:35:47.640+05:302013-02-04T22:35:47.640+05:30how do i use this for multiple columns
i get the f...how do i use this for multiple columns<br />i get the following<br /><br />Msg 1011, Level 16, State 1, Line 6<br />The correlation name 'Split' is specified multiple times in a FROM clause.<br /><br />when i do this<br /><br />-- Create Table for Sample Data <br />drop table test<br />CREATE TABLE Test <br />(ID INT, <br />AllNames VARCHAR(100),<br />lastnames VARCHAR(100)) <br />GO <br /><br />-- Load Sample Data <br />INSERT INTO test SELECT <br /> 1, 'A,B,C', '1,2,3' UNION ALL SELECT <br /> 2, 'A,B' , '7,8,9' UNION ALL SELECT <br /> 3, 'X,Y,Z', 't,u,v' <br />GO <br /><br />-- Verify the Sample Data <br />SELECT Id, AllNames, lastnames<br />FROM Test<br /><br />;WITH Cte AS <br />(SELECT <br /> id, <br /> CAST('' + REPLACE( Allnames, ',' , '') + '' AS XML) AS allNames ,<br /> CAST('' + REPLACE( lastnames, ',' , '') + '' AS XML) AS lastNames <br />FROM Test) <br />SELECT <br /> ID, <br /> Split.a.value('.', 'VARCHAR(100)') AS allNames,<br /> split.b.value('.', 'VARCHAR(100)') AS lastNames <br />FROM Cte <br /> CROSS APPLY allNames.nodes('/M') Split(a)<br /> CROSS APPLY lastNames.nodes('/M') Split(b)abnderbyhttps://www.blogger.com/profile/17189712471382729172noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-68604639223181493792012-07-21T04:36:39.055+05:302012-07-21T04:36:39.055+05:30Thank you, this was a great help.Thank you, this was a great help.Anonymoushttps://www.blogger.com/profile/08275850065778052929noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-45802382467123099802012-06-13T00:28:44.961+05:302012-06-13T00:28:44.961+05:30if you are using SQL 2000, you neither have XML......if you are using SQL 2000, you neither have XML...TimTimhttps://www.blogger.com/profile/17003725421306013400noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-13833204140342218022012-06-13T00:27:18.270+05:302012-06-13T00:27:18.270+05:30if you are using SQL2000, you neither have XML...if you are using SQL2000, you neither have XML...TimTimhttps://www.blogger.com/profile/17003725421306013400noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-10132007773918744852012-05-04T01:57:23.005+05:302012-05-04T01:57:23.005+05:30This solution is just really great, I've been ...This solution is just really great, I've been searching for something like this in for a while, only one problem though i'm using sql 2000 and it doesn't use the with clause. Is there any equivalent for sql 2000precieuxhttps://www.blogger.com/profile/06473754822621854236noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-54614892381000972622012-05-04T01:52:05.213+05:302012-05-04T01:52:05.213+05:30really Very Nicereally Very Niceprecieuxhttps://www.blogger.com/profile/06473754822621854236noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-20673804991021857922012-02-17T20:34:40.908+05:302012-02-17T20:34:40.908+05:30Awesome Solution , GreatAwesome Solution , GreatHarishhttps://www.blogger.com/profile/07180903081363895684noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-42862820329712899742012-02-17T20:34:29.736+05:302012-02-17T20:34:29.736+05:30Awesome, Genius :)Awesome, Genius :)Harishhttps://www.blogger.com/profile/07180903081363895684noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-90155648943752646122011-11-25T18:16:16.097+05:302011-11-25T18:16:16.097+05:30yeah,this is absolutely good but it is helpful,als...yeah,this is absolutely good but it is helpful,also if you are add the purpose of function why are you using there....Unknownhttps://www.blogger.com/profile/15068772836489779721noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-61242022231725099262011-10-15T23:26:47.093+05:302011-10-15T23:26:47.093+05:30you sir, are a genius. this is exactly what i nee...you sir, are a genius. this is exactly what i needed. other solutions did not work as elegantly as this. thank you for posting.Comfortably Numbhttps://www.blogger.com/profile/00228412120985202034noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-32435593556652600172011-09-24T02:11:25.328+05:302011-09-24T02:11:25.328+05:30i need something similiar like that , string :1010...i need something similiar like that , string :1010101111<br />it should tell the position of all the character in the string<br />like <br />1 1<br />0 2<br />1 3<br /><br />is it possiblekalpanahttps://www.blogger.com/profile/06598187369352044720noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-11810876317572350832011-08-05T01:54:44.833+05:302011-08-05T01:54:44.833+05:30Perfect! Best thing I have found. Thank you!Perfect! Best thing I have found. Thank you!Adam Mikolajhttps://www.blogger.com/profile/13057241657465527608noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-42581837945268782182011-05-03T06:58:17.431+05:302011-05-03T06:58:17.431+05:30Very Helpful. Saved alot timeVery Helpful. Saved alot timeVikas Bawejahttps://www.blogger.com/profile/06686076300729822480noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-20402712175187183942010-06-21T20:48:53.341+05:302010-06-21T20:48:53.341+05:30Very elegant solution - much better than creating ...Very elegant solution - much better than creating temp tables and such.Muljadi Budimanhttps://www.blogger.com/profile/12654522588096813100noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-39675092784530913052009-11-06T04:39:47.273+05:302009-11-06T04:39:47.273+05:30nice solution, thanks for posting it to your site....nice solution, thanks for posting it to your site.Unknownhttps://www.blogger.com/profile/11946448373697118997noreply@blogger.com