Databases
StackOverflow2013
Postsdbo
POparse comma delimited column to insert into table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POparse comma delimited column to insert into table
    primarykey
    data
    text
    <p>I have a table where the column looks like this:</p> <pre><code>Column 0 2013-11-27 13:11:00,1XRTT,DATA,East Michigan,Region 2,East Michigan_PORT HURON_CL#17,LNS1,2436,DE60XC049,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,,,,,,76,0,0,0,0,0,0,0,41,35,0,2.59444444444444444444444444444444444444,0,76,0,0,0,168,168,,,,,,,,,,,,,,,,,,,,,,,,,155.666666666666666666666666666666666667,0,0,0,0,0,3,0,104,0,0,0,150,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,7,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,18,0 </code></pre> <p>I'd like to parse it to another table with the columns for each of the delimiters. I already have the insert table created, but how do I parse it into the new table?</p> <p>based on the comment from Mate, I did this</p> <p>created a function like this:</p> <pre><code>IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitString_Using_CTE_Charindex]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[SplitString_Using_CTE_Charindex] GO CREATE FUNCTION SplitString_Using_CTE_Charindex (@csv_str VARCHAR(8000),@delimiter varchar(20) ) RETURNS @splittable TABLE (id int identity(1,1), csvvalues VARCHAR(8000) ) AS BEGIN -- Check for NULL string or empty sting IF (LEN(@csv_str) &lt; 1 OR @csv_str IS NULL) BEGIN RETURN END ; WITH csvtbl(i,j) AS ( SELECT i=1, j= CHARINDEX(@delimiter,@csv_str+@delimiter) UNION ALL SELECT i=j+1, j=CHARINDEX(@delimiter,@csv_str+@delimiter,j+1) FROM csvtbl WHERE CHARINDEX(@delimiter,@csv_str+@delimiter,j+1) &lt;&gt; 0 ) INSERT INTO @splittable ( csvvalues) SELECT SUBSTRING(@csv_str,i,j-i) FROM csvtbl RETURN END GO </code></pre> <p>then ran this:</p> <pre><code>DECLARE @csv_str VARCHAR(8000) ,@delimiter VARCHAR(20) SET @csv_str = (select * from testimport); SET @delimiter =',' SELECT * FROM dbo.SplitString_Using_Charindex(@csv_str,@delimiter) </code></pre> <p>but then I got this:</p> <pre><code>Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression. </code></pre> <p>I did a top 1 in the query too but got this then:</p> <pre><code>Msg 530, Level 16, State 1, Line 43 The statement terminated. The maximum recursion 100 has been exhausted before statement completion. </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USlightweight
    1. USlightweight
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCheck http://prosqlserver.com/blog/2011/02/05/parsing-csv-or-other-delimited-strings-in-sql-server/ . You could a SELECT INTO with function result
      singulars
      1. POparse comma delimited column to insert into table
      1. USMate
    2. COupdated the question Mate based on your suggestion...Thanks!
      singulars
      1. POparse comma delimited column to insert into table
      1. USlightweight
    3. COI guess "select * from testimport" returns more one result. You need a cursor or something to iterate results and call the function for each result... Try with "select top 1 * from testimport" and if works... go for cursor
      singulars
      1. POparse comma delimited column to insert into table
      1. USMate
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload