{"id":6481,"date":"2013-12-13T12:26:21","date_gmt":"2013-12-13T12:26:21","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/split-csv-entries-from-a-column-in-a-table\/"},"modified":"2016-07-28T10:54:32","modified_gmt":"2016-07-28T10:54:32","slug":"split-csv-entries-from-a-column-in-a-table","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/split-csv-entries-from-a-column-in-a-table\/","title":{"rendered":"Split csv entries from a column in a table"},"content":{"rendered":"<p>This is all in a single query without having to create a function. The separator is in a cte so doesn&#8217;t need to be declared separately. For longer separators include the length in this cte and use it for the length adjustments in the query.<\/p>\n<pre>create table #a (rowid int, csvstring varchar(100))\ninsert #a select 1, 'a,b,c,asd,yyyy'\ninsert #a select 2, 'x'\ninsert #a select 3, 'fhasgdff,wretyr'<\/pre>\n<pre>;with csv as (select s=',')\n, cte1 as\n(\nselect rowid=rowid, csvstring = csvstring + csv.s, strtloc = 1, endloc = charindex(csv.s,csvstring+csv.s)-1 from #a,csv where len(csvstring) &gt; 0\nunion all\nselect rowid, csvstring, strtloc = endloc+2, endloc = charindex(csv.s,csvstring,endloc+2)-1 from cte1,csv where charindex(csv.s,csvstring,endloc+2) &gt; 0\n)\n, cte as\n(\nselect rowid, str=substring(csvstring,strtloc, endloc-strtloc+1), strtloc\nfrom cte1\n)\nselect rowid, str\nfrom cte\norder by rowid, strtloc<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>This is all in a single query without having to create a function. The separator is in a cte so doesn&#8217;t need to be declared separately. For longer separators include the length in this cte and use it for the length adjustments in the query. create table #a (rowid int, csvstring varchar(100)) insert #a select&#8230;&hellip;<\/p>\n","protected":false},"author":143519,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-6481","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6481","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/143519"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=6481"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6481\/revisions"}],"predecessor-version":[{"id":42353,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6481\/revisions\/42353"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=6481"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=6481"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=6481"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=6481"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}