
TSQL Debugging problem
I want to create a Split function for use in my SQLSERVER.<BR>I wrote this code, but it's giving me an error. It says that the length parameter for the substring function is invalid, but it's not. Here is the code:<BR><BR>CREATE FUNCTION dbo.Split<BR> (<BR> @String varchar(100)<BR> )<BR>RETURNS @ArrayTable TABLE (arrayElement varchar(100))<BR>AS<BR> BEGIN<BR> DECLARE @i AS tinyint<BR> DECLARE @SPCpos AS tinyint<BR> DECLARE @tmpString AS varchar(100)<BR> DECLARE @Element AS varchar(100)<BR> DECLARE @SUBS_LEN As Int<BR> DECLARE @SUBS_START As Int<BR> <BR> SET @tmpString = @String<BR> SET @SPCpos = 0 <BR> SET @i = 0<BR> <BR> While @i < LEN(@String)<BR> BEGIN<BR> SET @SPCpos = CHARINDEX(' ',@tmpString)<BR> SET @Element = LEFT(@tmpString,@SPCpos  1)<BR> INSERT INTO @ArrayTable (arrayElement) Values(@Element)<BR> SET @i = @i + @SPCpos<BR> SET @SUBS_START = @SPCpos + 1<BR> SET @SUBS_LEN = LEN(@tmpString)  @SPCpos<BR> SET @tmpString = SUBSTRING(@tmpString,@SUBS_START,@SUBS_LEN),@SPCpos + 1,LEN(@tmpString)  @SPCpos)<BR> END<BR> <BR> RETURN<BR> END

I see one goof...
You aren't handling the last portion of the string, the part that comes after the last space! You'll get zero for SPCpos in that case and then try to do LEFT(@tmpString,1) !!!<BR><BR>Kablooey!<BR><BR>I don't see why it's blowing on the line you say, but if that part is wrong...<BR><BR>It seems to me there is a simpler way to do this, no need for the clumsy "i" counter:<BR><BR>WHILE CHARINDEX(' ',@tmpString) > 0<BR>BEGIN<BR> SET @SPCpos = CHARINDEX(' ',@tmpString)<BR> INSERT INTO @ArrayTable(arrayElement) VALUES( LEFT(@tmpString,@SPCpos1) )<BR> SET @tmpString = SUBSTRING(@tmpString,@SPCpos+1,Len(@tmpString)@SPCpos)<BR>END<BR>INSERT INTO @ArrayTable(arrayElement) VALUES(@tmpString)<BR>

Ummmm...that might be buggy, too...
What happens if the string *ends* with a space?<BR><BR>"a b "<BR><BR>len is 4, spcpos is 2. left is just "a"<BR>"a" gets inserted. <BR>Now the string is "b "<BR>len is 2, spcpos is 2. left is just "b"<BR>"b" gets inserted.<BR>But now you try to do <BR> SET @str = SUBSTRING(@str, 2+1, 22)<BR><BR>OOPS! Kablooey on the zero length substring!<BR><BR>So you need to either trim off the trailing spaces first or handle the case where SPCpos = Len(tmpString) !<BR><BR>

For that matter...
...I wonder if TSQL will handle any of this if you have multiple adjacent spaces!<BR><BR>If you do, the space will be found at position 1, so you'll be trying to insert a zerolength string into the array.<BR><BR>You might want to provide for this!<BR><BR>

Thanks Bill, you da man. <eom>
...still playing with it.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

