Solved

Split Column contents with CSV's

Posted on 2013-01-29
12
284 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
Comment Utility
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
Comment Utility
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
Comment Utility
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]
Comment Utility
What version of sql server?
What dba level?
I use this kind of syntax every day...
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:Rickzzz
Comment Utility
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]
Comment Utility
In that second example, the dbo. Part is missing...
0
 

Author Comment

by:Rickzzz
Comment Utility
"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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

763 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

8 Experts available now in Live!

Get 1:1 Help Now