question

NJSwede avatar image
NJSwede asked

Parsing out a column into multiple rows in new table.

Hello All - I'm new to the boards - and "semi" new to SQL, and am having difficulty in parsing out a column containing multiple values into separate rows. Here is a link for the DDL editor http://sqlfiddle.com/#!3/00388/3 I have a file of 100 records - where the 2nd field contains either 2 or 3 phones numbers (delimited by comma). I want to create a separate table where each phone# is written as a new row (so essentially a phone table which will be linked back by sku field). SO in this example - the first 8 records will generate two rows each (since 2 phone# in each field), and the 9th record will generate 3 rows, etc.. So something like: SKU COLUMN 1 101 (978) 371-3802 101 (781)344-1202 102 (508)650-9900 102 (781)793-0330 103 (781) 613-6100 103 (617)946-4800 . . . 109 (508)359-4107 109 (800)528-1530 x120 109 (781)489-1030 Any help is appreciates - Thanks!
parsing
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel avatar image
Squirrel answered
select t.sku, phone = stringval from showme t cross apply dbo.CSVTable(item) i am using a string parsing function CSVTable(), you can get it from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for quick reply - but I am getting an error while trying to run "Incorrect syntax near 'int'.: CREATE function CSVTable(@Str varchar(7000)) returns @t table (numberval int, stringval varchar(100), DateVal datetime) as begin declare @i int" http://sqlfiddle.com/#!3/824a4/1 Do I need to make some change to the function itself?
0 Likes 0 ·
are you using Microsoft SQL Server ?
0 Likes 0 ·
Yes - 2012
0 Likes 0 ·
on your SQL Management Studio, (not sqlfiddle) 1. copy and paste the CSVTable script, starting from CREATE FUNCTION to a query window and execute it. This will create the CSVTable function in your database. 2. execute the query i posted. It will give you the result that you need
0 Likes 0 ·
The solution is correct. However, there are better ways of creating a table from a CSV. A WHILE loop is not at all efficient. Re4ad this article: http://www.sqlservercentral.com/articles/Tally+Table/72993/
0 Likes 0 ·
NJSwede avatar image
NJSwede answered
Thanks for this!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.