I am creating a function in sql to parse a sting it to a variable number of sections base on a delimiter that is set by a parameter.
Below is the function.
ALTER FUNCTION [dbo].[L_SPLIT]
(
@P1 varchar(max), -- string to parse
@P2 int, -- number of sections
@P3 varchar(10),-- Delimiter
@P4 int –- section to return
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
Declare @products varchar(Max)
DECLARE @cleanInput varchar(max)
Declare @TEST VARCHAR(10)
Declare @I int
set @I =1
SET @TEST = '%'+ @P3+'%'
set @products = @P1
--WHILE LEN(@products) > 0
WHILE @I <= @P4 and @I <= @P2
BEGIN
SET @cleanInput = SUBSTRING(@products,
0,
PATINDEX(@TEST, @products))
SET @products = SUBSTRING(@products,
LEN(@cleanInput + @P2) + 1,
LEN(@products))
set @I = @I + 1
END
set @cleanInput = CASE
WHEN @P4 < @P2 THEN @cleanInput
Else @products
end
-- Return the result of the function
RETURN @cleanInput
END
When I Run
select
dbo.L_SPLIT('1|20|3|343|44|6|8765',3,'|',1)
it returns 1 which is correct
when I run
select
dbo.L_SPLIT('1|20|3|343|44|6|8765',3,'|',2)
it returns NULL and it should be 20
when I run select
dbo.L_SPLIT('1|20|3|343|44|6|8765',3,'|',3)
It returns |3|343|44|6|8765 it should be 3|343|44|6|8765
Open in new window