Solved

Split Column contents with CSV's

Posted on 2013-01-29
12
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 45

Expert Comment

by:Kent Olsen
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 143

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 143

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:Kent Olsen
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

628 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