The code below takes a single field that contains delimited text and splits it up and places it in adjacent fields depending on the amount of delimiters.
Example of the delimited text: OFFR0048|OFFR0046|OFFR0044|OFFR0042|OFFR0040|OFFR0038|OF03993|
The code is running fine however it takes a considerable amount of time to complete.
Can this process below be executed more efficiently?
--create procedure variables
declare @CONS varchar(150), @SINGLE varchar(20), @BCC int, @SQLText nvarchar(1000), @Count int
--create cursor
declare String_Split CURSOR for
select ADD_BARCODE from ADD_BARCODES --where (LEN(ADD_BARCODE) - LEN(REPLACE(ADD_BARCODE,'|',''))) >= 7
open String_Split --open cursor
fetch next from String_Split INTO @CONS --set cursor to the first row
WHILE @@FETCH_STATUS = 0 --start procedure
begin
set @BCC = 1 --set the string field to 1
while LEN(@cons) > 0 --start while there are addition codes to split
begin
if CHARINDEX('|',@CONS) > 0 --checks if there are strings to split
begin --begin compound statement 1
set @SINGLE = SUBSTRING(@cons,0,CHARINDEX('|',@CONS)) --use delimiter to split the string
set @SQLText = 'update ADD_BARCODES set ADD_BC' + CAST(@BCC as varchar)+' =
''' + @SINGLE + ''' WHERE CURRENT OF String_Split' --create dynamic query to update relevant string column
exec sp_executesql @SQLText --execute dynamic query
set @BCC = @BCC + 1 --increment string field with 1
set @CONS = SUBSTRING(@CONS, LEN(@SINGLE + '|') + 1,len(@CONS)) --set the remaining string to the @cons varianble for further processing
end --end compound statement 1
else --if there are not strings to split
begin --begin compound statement 2
set @SINGLE = @CONS --set @cons variable equal to the @single variable
set @CONS = null --execute dynamic query
set @SQLText = 'update ADD_BARCODES set ADD_BC' + CAST(@BCC as varchar)+' =
''' + @SINGLE + ''' WHERE CURRENT OF String_Split' --create dynamic query to update relevant string column
exec sp_executesql @SQLText --execute dynamic query
end --end compound statement 2
end --end while there are addition codes to split
fetch next from String_Split INTO @CONS --fetch next entry in cursor
end --end procedure
close String_Split --close cursor
deallocate String_Split --deallocate cursor memory
Perhaps you could use a parser function and then apply your updates in one statement with a join. I suspect one update statement would be more efficient that constructing and executing 7 (in this case)
Declare @DelStr varchar(max) = 'OFFR0048|OFFR0046|OFFR0044|OFFR0042|OFFR0040|OFFR0038|OF03993|'
Select * from [dbo].[udf-Str-Parse](@DelStr,'|') Where Key_Value<>'' Order by 1
Returns
Key_PS Key_Value
1 OFFR0048
2 OFFR0046
3 OFFR0044
4 OFFR0042
5 OFFR0040
6 OFFR0038
7 OF03993
There are millions of variations (some better/some worse), but here is my parser
CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
-- Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
-- Select * from [dbo].[udf-Str-Parse]('id26,id46|id658,id967','|')
Returns @ReturnTable Table (Key_PS int IDENTITY(1,1) NOT NULL , Key_Value varchar(max))
As
Begin
Declare @intPos int,@SubStr varchar(max)
Set @IntPos = CharIndex(@delimeter, @String)
Set @String = Replace(@String,@delimeter+@delimeter,@delimeter)
While @IntPos > 0
Begin
Set @SubStr = Substring(@String, 0, @IntPos)
Insert into @ReturnTable (Key_Value) values (@SubStr)
Set @String = Replace(@String, @SubStr + @delimeter, '')
Set @IntPos = CharIndex(@delimeter, @String)
End
Insert into @ReturnTable (Key_Value) values (@String)
Return
End