• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 123
  • Last Modified:

SQL Function not working properly

I have the following function that seems to work outside of the fact that if my list gets to long it doesn't seem to pull the propertyid's after a certain point.  Can anyone tell my why this would work 'HC,SM,WG,KG,CL,NH,CM,CH,CW,GO,KA,RL,SO,OF,WP,CG,PP,BF,CS' but this will not
'HC,SM,WG,KG,CL,NH,CM,CH,CW,GO,KA,RL,SO,OF,WP,CG,PP,BF,CS,DT,HT,HP,L1,L2,BW,B1,B2,BR,WA,WN,WT,SP,ME,JP,PV'

Here is the code and
ALTER Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(max) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(max)) 
as begin 
DECLARE @Item Varchar(max) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 

Open in new window


thanks in advance for the help
0
mgmhicks
Asked:
mgmhicks
1 Solution
 
Scott PletcherSenior DBACommented:
Worked fine for me.  Make sure the variable you're putting the values back into is correctly coded.

Btw, you should do a Google search for 8K Splitter, edited by Jeff Moden, which is an extremely efficient, fully-tested split function.
0
 
mgmhicksAuthor Commented:
You were correct it was the procedure that called the function that had the problem.  Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now