Inserting rows into a table from a string list; a better way to do it?
-
I have a stored procedure that receives a list of IDs in a string; each ID separated by a semicolon. The list may contain up to several hundred IDs. I need to write each of these IDs to a table (one per row). My solution follows:
DECLARE @IDs varchar(8000)
SET @IDs = '99;23;43;54445;765;67545;4535;7578;47745436;56545;353447;4545745;'
DECLARE @Pos smallint
SET @Pos = 1
WHILE (@Pos > 0 )
BEGIN
PRINT SUBSTRING( @IDs, @Pos, CHARINDEX( ';', @IDs, @Pos ) - ( @Pos ) )
SET @Pos = CHARINDEX( ';', @IDs, @Pos ) + 1
IF CHARINDEX( ';', @IDs, @Pos + 1 ) = 0 BREAK
END
Please notice that I have not included any logic to check if the string is empty and that the print statement will be substituted with an INSERT
Is there a "more efficient" way to do this?
-
Not much. I've posted this function before but its what we use for this situation. The performance benefit is that it eliminates one CHARINDEX call per loop. Of course, if you keep it as a function returning a temp table peformance wise it won't likely be any better that what you proposed, but it will at least look a lot nicer and be easy to reuse. You could, of course, code it directly into your SP as well and avoid the temp table and function call if you preferred. I don't think there is any magical solution that reduces the string manipulation any furthor, but I would, of course, be interested if someone figured something out.
CREATE FUNCTION Split
(@List varchar(8000)
@Delim char(1))
RETURNS @Results table
(Item varchar(8000))
AS
begin
declare @IndexStart int
declare @IndexEnd int
declare @Length int
declare @Word varchar(8000)
declare @Kill int
set @IndexStart = 1
set @IndexEnd = 0
set @Length = len(@List)
set @Kill = 0
while @IndexStart <= @Length
begin
set @Kill = @Kill + 1
if @Kill >= 999 return -- hard limiter just in case
set @IndexEnd = charindex(@Delim, @List, @IndexStart)
if @IndexEnd = 0
set @IndexEnd = @Length + 1
set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
set @IndexStart = @IndexEnd + 1
INSERT INTO @Results
SELECT @Word
end
return
end
-
There is a set based method to do this - but you would need to create a permanent table created that has one column having 8000 rows with values 1 to 8000. That is because the string could have a maximum of 8000 chars.
In the script below - the assumed table is called Numbers.
DECLARE @IDs varchar(8000)
SET @IDs = '99;23;43;54445;765;67545;4535;7578;47745436;56545;353447;4545745;'
--to remove the last semicolon
set @IDs = left(@IDs, len(@IDs) -1)
SELECT SUBSTRING(';' + @IDs + ';', Number + 1,
CHARINDEX(';', ';' + @IDs + ';', Number + 1) - Number - 1) as EachID
FROM Numbers
WHERE SUBSTRING(';' + @IDs + ';', number, 1) = ';'
AND Number < LEN(';' + @IDs + ';')
-
Aaron and CK,
Thanks for your responses.
CK that is quite an interesting solution. I am still scratching my head trying to understand it but I love its simplicity.
Vicar
-
I have the same type UDF (user defined function)
Insert into MyRealTable ( someField1 )
SELECT ITEM FROM dbo.udf_10_comma_delimited_char('abc,def,ghi,jkl' , ',' )
Its as simple as that. The 8000 is the total allowed length of the entire string.
Your field can be as small as it needs to be. Ex. If "someField1" were only 16 chars long (varchar(16)), then the code will work fine.
The code is designed for the "worst case scenario". Your real fields can be as small as necessary.
if exists
(select * from sysobjects where id = object_id('dbo.udf_10_comma_delimited_char') and xtype = 'TF')
drop function dbo.udf_10_comma_delimited_char
GO
CREATE
FUNCTION dbo.udf_10_comma_delimited_char(@list varchar(8000), @Delimiter VARCHAR(10) = ',')
/*
Original Need : By passing in a delimited set of values
The code will return a table with the items seperated
Sample Usage :
SELECT ITEM
FROM dbo.udf_10_comma_delimited_char('abc,def,ghi,jkl' , ',' )
ORDER BY ITEM DESC --or whatever WHERE, GROUP BY, HAVING, ORDER BY clause you can muster up
Notes :
*/
RETURNS
@tablevalues TABLE
(item
varchar(8000))
AS
BEGIN
DECLARE @P_item varchar(255)
WHILE (DATALENGTH(@list) > 0)
BEGIN
IF CHARINDEX(@Delimiter,@List) > 0
BEGIN
SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + DATALENGTH(@Delimiter)),DATALENGTH(@List))
END
ELSE
BEGIN
SELECT @p_Item = @List
SELECT @List = NULL
END
INSERT INTO @tablevalues
SELECT Item = @p_Item
END
RETURN
END
GO
--GRANT EXECUTE ON udf_10_comma_delimited_char TO someUser
if exists
(select * from sysobjects where id = object_id('dbo.udf_10_comma_delimited_int') and xtype = 'TF')
drop function dbo.udf_10_comma_delimited_int
GO
CREATE FUNCTION
dbo.udf_10_comma_delimited_int(@list varchar(8000), @Delimiter VARCHAR(10) = ',')
RETURNS
@tablevalues TABLE (item int)
AS
/*
Original Need : By passing in a delimited set of (int) values
The code will return a table with the items seperated
Sample Usage :
SELECT ITEM
FROM dbo.udf_10_comma_delimited_int('1,3,5,6' , ',' )
ORDER BY ITEM DESC --or whatever WHERE, GROUP BY, HAVING, ORDER BY clause you can must up
Notes :
Mimics the 'dbo.udf_10_comma_delimited_char' procedure
*/
BEGIN
DECLARE @P_item varchar(255)
WHILE (DATALENGTH(@list) > 0)
BEGIN
IF CHARINDEX(@Delimiter,@List) > 0
BEGIN
SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + DATALENGTH(@Delimiter)),DATALENGTH(@List))
END
ELSE
BEGIN
SELECT @p_Item = @List
SELECT @List = NULL
END
INSERT INTO @tablevalues
SELECT Item = convert(int,@p_Item)
END
RETURN
END
GO
--GRANT EXECUTE ON udf_10_comma_delimited_int TO someUser
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply