Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Split Column contents with CSV's

Posted on 2013-01-29
12
Medium Priority
?
303 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 1000 total points
ID: 38831476
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 46

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 46

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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