CHARINDEX

SQL Server 2000

Ya know, it is always the simplest stuff that gets ya !!

I am having the hardest time getting a simple piece of code working.
Must be brain dead today.

Goal: Get the users full name from a string

Here is sample data:

"LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"

Code:
IF LEN(@.strReturnValue) > 0 BEGIN
SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
(CHARINDEX('CN=',@.strReturnValue)+3),
(CHARINDEX(',',@.strReturnValue)-1))
END

It will extract:
"Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"

I want it to extract:
Kevin Jones

Thanks.On 23 Jun 2005 08:51:27 -0700, csomberg@.dwr.com wrote:

> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))

declare @.test varchar(255)
declare @.pos int

select @.test = 'LDAP://blahblahblah/CN=Kevin
Jones,OU=DevEng,DC=nobody,DC=priv,DC=com'

select @.pos = CHARINDEX('CN=',@.test)+3

SELECT SUBSTRING(@.test, @.pos ,(CHARINDEX(',',@.test)) - @.pos)

Tony
--
http://www.dotnet-hosting.com
Free web hosting with ASP.NET & SQL Server
No ads - No trials - Innovative features|||...oops i should have written:

You're thinking the substring syntax is
substring( string, start, end )

when the correct syntax is
substring( string, start, length )

where length is end_position - start position

Sorry for the confusion.

hth,

victor dileo

csomberg@.dwr.com wrote:
> SQL Server 2000
> Ya know, it is always the simplest stuff that gets ya !!
> I am having the hardest time getting a simple piece of code working.
> Must be brain dead today.
> Goal: Get the users full name from a string
> Here is sample data:
> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))
> END
> It will extract:
> "Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> I want it to extract:
> Kevin Jones
> Thanks.|||Note the charindex syntax:
charindex ( string, start, length )

You're mistakenly thinking the syntax is:
charindex ( string, start, end )

The "end" parameter should actually be length from the "start"
position, and be calculated as something like:
length = end - start

Once you fix that, I think your code will work just fine.

hth,

victor dileo

csomberg@.dwr.com wrote:
> SQL Server 2000
> Ya know, it is always the simplest stuff that gets ya !!
> I am having the hardest time getting a simple piece of code working.
> Must be brain dead today.
> Goal: Get the users full name from a string
> Here is sample data:
> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))
> END
> It will extract:
> "Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> I want it to extract:
> Kevin Jones
> Thanks.