SUBSTRING limitation

[StuartBombay]

I know I may catch some flack for parsing strings in a SP, but it must be done.
I'm having an issue with SUBSTRING, in the following code, if I up the '23' to '24' I get no results. If I up it to '25' I get the error
"Invalid length parameter passed to the SUBSTRING function"

DECLARE @TheOU varchar(5000),
		@OU varchar(5000)
SET @OU = 'uid=sabinguy,OU=staff,ou=sabin,OU=ES,o=company'
select
		SUBSTRING(@OU,                
			CHARINDEX(',',@OU)+ 23,    
			(CHARINDEX(',',@OU,CHARINDEX(',',@OU)+23)) - (CHARINDEX(',',@OU)+23))

I've done some research and found the SUBSTRING seems to have limitations in SQL2005. OK, I still need parse this string, but I'm at a loss.

The real code, by the way, didn't use the number 25,
(LEN(@OU)+8 was there. I'm trying to find the top most OU, unless that OU is IN('staff', 'itinerant'), in that case I need the next OU down.
Has anyone found a solution for this? It took me half a day to get this far... now I'm crushed to find it has this limitation!

 

[ousoonerjoe]

Check out the following thread from a few months ago. The SQL Functions listed in there by gmmastros solved a parsing issue i had.

thread183-1451986

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[StuartBombay]

Thank you.
I had read about SQL2005 limitations so jumped to the conclusion that it wasn't me (!). But I reduced the parse down to:

declare @pos1 int
declare @pos2 int
declare @pos3 int
declare @len int
declare @doc varchar(100)
select @pos1 = charindex(',','uid=superuser,ou=IT,ou=support,o=company',(charindex(',','uid=superuser,ou=IT,ou=support,o=company'))+4)+4
select @pos2 = charindex(',','uid=superuser,ou=IT,ou=support,o=company', @pos1)
select @len = @pos2 - @pos1
select @doc = substring('uid=superuser,ou=IT,ou=support,o=company', @pos1, @len)
print  @pos1 print @pos2 print @len print @doc

Then to:

declare @OU varchar(250), @out varchar(250)
set @OU = 'uid=superuser,ou=IT,ou=support,o=company'
set @out=
(
SELECT
SUBSTRING(@OU,
(24),(31)-(24))
)
print @out

By replacing the numbers with the position functions in the first chunk of code I finally got it right:

declare @OU varchar(250), @out varchar(250)
set @OU = 'uid=superuser,ou=IT,ou=support,o=company'
set @out=
		(
 		SELECT 
 		SUBSTRING(@OU,                
 		(charindex(',',@OU,(charindex(',',@OU))+4)+4),
		((charindex(',',@OU, (charindex(',',@OU,(charindex(',',@OU))+4)+4)))-
			(charindex(',',@OU,(charindex(',',@OU))+4)+4)))	
		)
print @out

It's a painful exercise but the best way to go about this sort of thing. It's how I got to it when I thought I would only need the first OU, when I found out there are cases where I need the second... I took a shortcut. I should never take shortcuts. It's a good argument for commenting/documenting code too.
Hope someone else finds this useful.