Dovberman
asked on
tsql to Parse a comma delimited list
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
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,
-- 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
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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,
SET @List = 'ABC,DEF,GHIJK,LMNOPQRS,T,
-- 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
ASKER
pourfard,
Your solution is simple and it worked for me.
Your solution is simple and it worked for me.
ASKER
pateljitu,
Where did you place the INSERT INTO Statement?
Thanks,
Where did you place the INSERT INTO Statement?
Thanks,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
notice that the Insert statement was removed from the stored procedure during many failed editing attempts.
Where should I now replace it?
Thanks
Where should I now replace it?
Thanks
ASKER
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,
Suppose I need to insert two columns.
DECLARE @List VARCHAR(8000), @ListPrice VARCHAR(8000), @Sql VARCHAR(MAX)
SET @List = 'ABC,DEF,GHIJK,LMNOPQRS,T,
SET @ListPrice = '27.1,24.5,12.3,34.5,67.1,
-- 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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
pateljitu,
Thank you.
The actual delimited string has 1800 values.
Will performance be affected?
Thank you.
The actual delimited string has 1800 values.
Will performance be affected?
ASKER
Thanks to everyone
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.
But if delimited string value increases to 4000+, performance will have issues.
ASKER
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,
Thanks,