Microsoft SQL Server forum

Author

CHARINDEX

csomberg@dwr.com

2005-06-23, 11:23 am

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=n
obody,DC=priv,DC=com
"

Code:
IF LEN(@strReturnValue)
> 0 BEGIN
SELECT @strReturnValue = SUBSTRING(@strReturn
Value,
(CHARINDEX('CN=',@st
rReturnValue)+3),
(CHARINDEX(',',@strR
eturnValue)-1))
END

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

I want it to extract:
Kevin Jones

Thanks.

Karim

2005-06-23, 1:23 pm

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

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

declare @test varchar(255)
declare @pos int

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

select @pos = CHARINDEX('CN=',@tes
t)+3

SELECT SUBSTRING(@test, @pos , (CHARINDEX(',',@te
st)) - @pos)

Tony
--
http://www.dotnet-hosting.com
Free web hosting with ASP.NET & SQL Server
No ads - No trials - Innovative features

vjdileo

2005-06-23, 1:23 pm

....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=n
obody,DC=priv,DC=com
"
>
> Code:
> IF LEN(@strReturnValue)
> 0 BEGIN
> SELECT @strReturnValue = SUBSTRING(@strReturn
Value,
> (CHARINDEX('CN='
,@strReturnValue)+3)
,
> (CHARINDEX(',',@
strReturnValue)-1))
> END
>
> It will extract:
> "Kevin Jones,OU=DevEng,DC=n
obody,DC=priv,DC=com
"
>
> I want it to extract:
> Kevin Jones
>
> Thanks.

vjdileo

2005-06-23, 8:23 pm

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=n
obody,DC=priv,DC=com
"
>
> Code:
> IF LEN(@strReturnValue)
> 0 BEGIN
> SELECT @strReturnValue = SUBSTRING(@strReturn
Value,
> (CHARINDEX('CN='
,@strReturnValue)+3)
,
> (CHARINDEX(',',@
strReturnValue)-1))
> END
>
> It will extract:
> "Kevin Jones,OU=DevEng,DC=n
obody,DC=priv,DC=com
"
>
> I want it to extract:
> Kevin Jones
>
> Thanks.

Sponsored Links