Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Function not working properly

Posted on 2015-01-05
2
107 Views
Last Modified: 2015-01-05
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
Comment
Question by:mgmhicks
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40531799
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
 

Author Closing Comment

by:mgmhicks
ID: 40531865
You were correct it was the procedure that called the function that had the problem.  Thank you
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql how to count case when 4 24
Conver SQL Server 2008 R2 from sql_latin1_general_cp1_ci_as to Arabic_CI_AS 9 21
TSQL Challenge... 7 35
Help with Oracle IF statment 5 22
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question