CHARINDEX

[csomberg]

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.

[Karim]

On 23 Jun 2005 08:51:27 -0700, cs******@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

[vjdileo]

....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

cs******@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.

[vjdileo]

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

cs******@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.