Solved

Split Column contents with CSV's

Posted on 2013-01-29
12
287 Views
Last Modified: 2013-01-29
The table (Auto) contains a column (vehicleList) with comma delimited vehicleID's. Example row:

autoID      vehicleList
2259063      3875473,4011705

Need to query the table, parsing the delimited string in the column vehicleList, so that the following is returned if select where autoID = 2259063 for example:

autoID      vehicleID
2259063      3875473
2259063      4011705

The vehicleList column may contain just one or several comma separated vehicleID's

Thanks in advance!
0
Comment
Question by:Rickzzz
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 38831404
Check out fn_split, a user-defined function that's been promoted on the web.

http://msdn.microsoft.com/en-us/library/aa496058%28v=sql.80%29.aspx
0
 
LVL 9

Accepted Solution

by:
mimran18 earned 250 total points
ID: 38831476
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 38831487
I had created this function dbo.ParmsTolist:
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html

which you would use like this:
select t.autoID  , f.value Vehicle
from Auto a
cross apply dbo.ParmsToList(a.vehicleList, ',') f

Open in new window

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 45

Expert Comment

by:Kdo
ID: 38831719
And you can do this with recursive SQL.  Some shops don't allow UDFs for this kind of testing / reporting on the production systems.


  Here's a link to an EE article that explains how it's done:

http://www.experts-exchange.com/Database/DB2/A_3629-Recursive-SQL-in-DB2-Converting-Columns-to-Rows.html
0
 

Author Comment

by:Rickzzz
ID: 38831745
Thanks everyone.

angelIII, I get this result when I run the statement below:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.
select
	a.autoID,
	f.value vehicleID
from
	Auto a
		cross apply dbo.ParmsToList(a.vehicleList, ',') f
where
	a.autoID = 2259063

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38831829
What version of sql server?
What dba level?
I use this kind of syntax every day...
0
 

Author Comment

by:Rickzzz
ID: 38831937
SQL Server 2012. Just created the function and ran it on another box that is SQL 2008 with the same result.

Created UDF, just cut & paste yours.

Executed this exact select:
select
	a.autoID,
	f.value Vehicle
from
	Auto a
		cross apply ParmsToList(a.vehicleList, ',') f
where
	a.autoID = 2259063 

Open in new window

Again, erring at the call:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38831951
In that second example, the dbo. Part is missing...
0
 

Author Comment

by:Rickzzz
ID: 38832030
"cross apply dbo.ParmsToList(a.vehicleList, ',') f"
Same result.

I checked to make sure I wasn't messing up any formatting while copying over the function from your post and it doesn't look like it.
-- MS SQL Server
CREATE FUNCTION dbo.ParmsToList 
(@Parameters varchar(8000), @delimiter varchar(10) )
-- SQL 2005+ version would change the varchar(8000) into varchar(max)
returns @result TABLE (Value varchar(8000), row_num int identity )
AS  
begin
  declare @dx varchar(9)
 
  if @delimiter is null  set @delimiter = ' '
  if datalength(@delimiter) < 1 set @delimiter = ' '
  set @dx = left(@delimiter, datalength(@delimiter)-1)
 
  declare @Value varchar(8000), @Pos int
 
  set @Parameters = @Parameters + @delimiter
  set @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
  IF REPLACE(@Parameters, @delimiter, @dx) <> ''
  BEGIN
    WHILE @Pos > 0 
    BEGIN
      SET @Value = LEFT(@Parameters, @Pos - 1)
      IF @Value <> ''
      BEGIN
        INSERT INTO @result (Value) 
        VALUES (@Value) 
      END
      SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),8000)
      SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
    END --WHILE @Pos > 0 
  END -- IF REPLACE(@Parameters, @delimiter, @dx) <> ''
   
  RETURN
END
                                    

Open in new window

0
 
LVL 45

Expert Comment

by:Kdo
ID: 38832170
Hi Rick,

I don't mean to confuse things by changing approaches in the middle, but the recursive SQL below works just fine, without creating the UDF.

WITH pquery (wordnum, word, remainder)
AS
(
  SELECT 1, 
    CASE WHEN charindex (',', sentence) > 0 THEN 
      SUBSTRING (sentence, 1, charindex (',', sentence) - 1)
    ELSE
      sentence
    END word,
    CASE WHEN charindex (',', sentence) > 0 THEN
      RIGHT (sentence, len (sentence) - charindex (',', sentence))
    ELSE
      NULL
    END remainder
  FROM (select 'aaa,bbb,ccc,ddd') base (sentence)
  
  UNION ALL
  
  SELECT wordnum + 1, 
    CASE WHEN charindex (',', remainder) > 0 then 
      SUBSTRING (remainder, 1, charindex (',', remainder) - 1)
    ELSE
      remainder
    END word,
    CASE WHEN charindex (',', remainder) > 0 THEN
      RIGHT (remainder, LEN (remainder) - charindex (',', remainder))
    ELSE
      NULL
    END remainder
  FROM pquery t0
  WHERE t0.remainder IS NOT NULL
)
SELECT wordnum, word
FROM pquery pq;

Open in new window


Just change line 15 to select your text (with the column aliased to "SENTENCE").
                                   

Kent
0
 

Author Closing Comment

by:Rickzzz
ID: 38832708
Thanks mimran, that'll work great. I'm sure angels's does too, given the source, but I must have flubbed something somewhere. Would have preferred the UDF, but can't get it to work.
0
 

Author Comment

by:Rickzzz
ID: 38832837
Just to close it out cleanly.

Answer from mimran18:
http://raresql.com/2012/02/14/split-function-in-sql-server/

Query to accomplish the task:
Declare @delimiter VARCHAR(50) = ','

WITH
   CTE AS
      (SELECT
        autoID,
        -- Replace the delimiter to the opeing and closing tag to make it an xml document
        CAST('<M>' + REPLACE([vehicleList], @delimiter , '</M><M>') + '</M>' AS XML) vehicleID
    FROM
       [Auto]
    WHERE
       autoID = 2259063)

Select
    autoID,
    --Query this xml document via xquery to split rows
    Split.a.value('.', 'VARCHAR(MAX)') vehicleList
FROM
    CTE

CROSS APPLY [vehicleID].nodes('/M')Split(a)

Open in new window

Result as needed:
autoID      vehicleList
2259063      3875473
2259063      4011705
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now