troubleshooting Question

section a string

Avatar of Stacey Fontenot
Stacey Fontenot asked on
Microsoft SQL Server
5 Comments1 Solution138 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Dustin Saunders
Co-Founder and Chief Architect
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros