?
Solved

tsql to Parse a comma delimited list

Posted on 2014-01-03
14
Medium Priority
?
516 Views
Last Modified: 2014-01-06
I need to parse a comma delimited list in a stored procedure and Insert the parsed values into a table.

I copied from the following reference:

http://www.sqlbook.com/SQL/Parse-a-comma-delimited-list-8.aspx

The query ran but did not insert the parsed substring into a table.
The actual insert statement was not included in the article.

-- in the real world you would now do something with the list item
       -- * inserting it into a table

This is my stored proc:
  ALTER PROCEDURE [dbo].[usp_getEODExtract]
AS
DECLARE @List nvarchar(100)
DECLARE @ListItem nvarchar(10)  
DECLARE @Pos int

SET @List = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'

-- Loop while the list string still holds one or more characters
WHILE LEN(@List) > 0
Begin
       -- Get the position of the first comma (returns 0 if no commas left in string)
       SET @Pos = CHARINDEX(',', @List)

       -- Extract the list item string
       IF @Pos = 0
       Begin
               SET @ListItem = @List
       End
       ELSE
       Begin
               SET @ListItem = SUBSTRING(@List, 1, @Pos - 1)
       End
--http://www.sqlbook.com/SQL/Parse-a-comma-delimited-list-8.aspx
       -- in the real world you would now do something with the list item
       -- * inserting it into a table
       -- * using it as a parameter in a stored procedure call
       -- * parsing it into a numeric / datetime format to use in a calculation
       -- etc....
      INSERT INTO StockDataWork (SymbolName) Values(@ListItem)
       -- remove the list item (and trailing comma if present) from the list string
       IF @Pos = 0
       Begin
               SET @List = ''
       End
       ELSE
       Begin
               -- start substring at the character after the first comma
                SET @List = SUBSTRING(@List, @Pos + 1, LEN(@List) - @Pos)
       End
       
       
End

After execution: Command(s) completed successfully.

SELECT * FROM StockDataWork returned 0 rows.

 INSERT INTO StockDataWork (SymbolName) Values('abc') works

What do I need to change?

Thanks
0
Comment
Question by:Dovberman
[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
14 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 39753894
your procedure works correct.. no error exists. I tried and inserted all the values to the table.  Make sure you are working an the same database, I mean the procedure and the table you are querying are the same objects you are referencing.
0
 
LVL 12

Assisted Solution

by:Habib Pourfard
Habib Pourfard earned 400 total points
ID: 39753946
Try the following code:
DECLARE @List VARCHAR(8000), @Sql VARCHAR(MAX) 

SET @List = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'

CREATE TABLE #TempListTable ( [Value] VARCHAR(128) )

SELECT  @Sql = 'INSERT INTO #TempListTable ([Value]) Values (''' + REPLACE(@List, ',', '''),(''') + ''')'
EXEC (@Sql)

INSERT INTO StockDataWork (SymbolName) 
SELECT [Value] FROM #TempListTable

IF OBJECT_ID('tempdb..#TempListTable') IS NOT NULL DROP TABLE #TempListTable

Open in new window

0
 
LVL 15

Assisted Solution

by:pateljitu
pateljitu earned 1600 total points
ID: 39754068
As mentioned by @tigin44 your store procedure is working as expected.

How are you executing your store procedure i.e. are you trying via SQL management console or code.

As I tried executing SP from t-sql exec usp_getEODExtract and after execution I get output "(1 row(s) affected)" [8 times for each comma seperated value].

Confirm if you have proper permission is executing store procedure from code.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:Dovberman
ID: 39754092
tigin44

This is my entire sp:

USE [stockprosql]
GO
/****** Object:  StoredProcedure [dbo].[usp_getEODExtract]    Script Date: 01/03/2014 09:46:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      Appends to the end of day SQL Server work table from
--                        the downloaded end of day text table
--
-- =============================================
ALTER PROCEDURE [dbo].[usp_getEODExtract]
AS
declare @ListSymbolName VARCHAR(MAX)
declare @ListOpenPrice VARCHAR(MAX)
DECLARE @List nvarchar(100)
DECLARE @ListItem nvarchar(10)  
DECLARE @Pos int
DECLARE @ListItem1 nvarchar(10)  


--set @ListSymbolName = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'

SET @List = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'

-- Loop while the list string still holds one or more characters
WHILE LEN(@List) > 0
Begin
       -- Get the position of the first comma (returns 0 if no commas left in string)
       SET @Pos = CHARINDEX(',', @List)

       -- Extract the list item string
       IF @Pos = 0
       Begin
               SET @ListItem = @List
       End
       ELSE
       Begin
               SET @ListItem = SUBSTRING(@List, 1, @Pos - 1)
       End
--http://www.sqlbook.com/SQL/Parse-a-comma-delimited-list-8.aspx
       -- in the real world you would now do something with the list item
       -- * inserting it into a table
       -- * using it as a parameter in a stored procedure call
       -- * parsing it into a numeric / datetime format to use in a calculation
       -- etc....
     
       -- remove the list item (and trailing comma if present) from the list string
       IF @Pos = 0
       Begin
               SET @List = ''
       End
       ELSE
       Begin
               -- start substring at the character after the first comma
                SET @List = SUBSTRING(@List, @Pos + 1, LEN(@List) - @Pos)
       End
       
End

What did you change to make it work?

Thanks
0
 

Author Comment

by:Dovberman
ID: 39754100
pourfard,

Your solution is simple and it worked for me.
0
 

Author Comment

by:Dovberman
ID: 39754105
pateljitu,

Where did you place the INSERT INTO Statement?

Thanks,
0
 
LVL 15

Assisted Solution

by:pateljitu
pateljitu earned 1600 total points
ID: 39754139
Insert statement is within your store procedure so basically when you execute this t-sql line exec usp_getEODExtract in SQL management studio it will take care loop through the comma separated values and insert the same in database table.
0
 

Author Comment

by:Dovberman
ID: 39754153
notice that the Insert statement was removed from the stored procedure during many  failed editing attempts.

Where should I now replace it?

Thanks
0
 

Author Comment

by:Dovberman
ID: 39754162
pourfard,

Suppose I need to insert two columns.

DECLARE @List VARCHAR(8000), @ListPrice VARCHAR(8000), @Sql VARCHAR(MAX)

SET @List = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'
SET @ListPrice = '27.1,24.5,12.3,34.5,67.1,74.2,37.1,12.8'

-- For 1 column as you stated:

SELECT  @Sql =
'INSERT INTO #TempListTable ([Value]) Values (''' + REPLACE(@List, ',', '''),(''') + ''')'

What would the statement be for inserting the @List value and @ListPrice value ?

Thanks,
0
 
LVL 15

Accepted Solution

by:
pateljitu earned 1600 total points
ID: 39754189
Your SP should look as code below with insert statement:

-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      Appends to the end of day SQL Server work table from
--                        the downloaded end of day text table
-- 
-- =============================================
ALTER PROCEDURE [dbo].[usp_getEODExtract]
AS
declare @ListSymbolName VARCHAR(MAX)
declare @ListOpenPrice VARCHAR(MAX)
DECLARE @List nvarchar(100)
DECLARE @ListItem nvarchar(10)  
DECLARE @Pos int
DECLARE @ListItem1 nvarchar(10)  


--set @ListSymbolName = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'

SET @List = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'

-- Loop while the list string still holds one or more characters
WHILE LEN(@List) > 0
Begin
       -- Get the position of the first comma (returns 0 if no commas left in string)
       SET @Pos = CHARINDEX(',', @List)

       -- Extract the list item string
       IF @Pos = 0
       Begin
               SET @ListItem = @List
       End
       ELSE
       Begin
               SET @ListItem = SUBSTRING(@List, 1, @Pos - 1)
       End
	   --http://www.sqlbook.com/SQL/Parse-a-comma-delimited-list-8.aspx
       -- in the real world you would now do something with the list item
       -- * inserting it into a table
       -- * using it as a parameter in a stored procedure call
       -- * parsing it into a numeric / datetime format to use in a calculation
       -- etc....
		
      INSERT INTO testing (Name) values(@ListItem)
       -- remove the list item (and trailing comma if present) from the list string
       IF @Pos = 0
       Begin
               SET @List = ''
       End
       ELSE
       Begin
               -- start substring at the character after the first comma
                SET @List = SUBSTRING(@List, @Pos + 1, LEN(@List) - @Pos)
       End
       
End

Open in new window

0
 

Author Comment

by:Dovberman
ID: 39754346
pateljitu,

Thank you.

The actual delimited string has 1800 values.

Will performance be affected?
0
 

Author Closing Comment

by:Dovberman
ID: 39754549
Thanks to everyone
0
 
LVL 15

Expert Comment

by:pateljitu
ID: 39760062
With 1800 values performance would not be affected much (total execution should be done in 2 seconds), only change in your SP would be from declaration DECLARE @List nvarchar(100) to DECLARE @List nvarchar(MAX).

But if delimited string value increases to 4000+, performance will have issues.
0
 

Author Comment

by:Dovberman
ID: 39760154
Yes, Execution time for 1800 rows is 2 seconds.  Another sample with 3200 rows is about 3 seconds.  The max file I will process is about 3200 rows.

Thanks,
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 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