Solved

Split Column contents with CSV's

Posted on 2013-01-29
12
291 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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