CHARINDEX and STUFF/REPLACE

Hello,

I need to be able to replace only the first occurance of a space character
in a column.
Reason being is the data in the column I am trying to replace seems to have
umpteen space characters after each entry, so a simple replace function
will replace all the spaces after it with what I want!

I have thought of RTRIM to get rid of the spaces after and then replace, I
have also thought of CHARINDEX to find the first occurance of a space and
STUFF to replace it.
I have done my homework on these functions!
But I am having trouble writing such a statement,
I've never written a query which would use more then one function on one
column you see and I am getting confused!

I'll tell you what I want to do in simple steps

Replace only the first found space in a name column, but then if a name has
a middle initial that will be a problem,
Replace that with a dot.
then concatanate '@.emailaddress;@.emailaddress2' after it
so when SQLServer does the select it will bring back something like

joe.bloggs@.emailaddress;emailaddress
But I guess I'd also need joe.n.bloggs@.emailaddress;emailaddress

The data in the column looks like this at the moment

joe bloggs
But I guess there may come a time when we have
joe n bloggs, just to complicate things!

What is your advice, and how do I write a query like this
I have been playing around with it in Query Analyser but as I said I am
getting confused and need some help if you don't mind

Thanks a lot to all who reply :-)

Regards

JayneHi

Try:

SELECT CHARINDEX(SPACE(1),[String] ) AS [Position],[String],
STUFF ( [String], CHARINDEX(CHAR(32),[String] ), 1, SPACE(0) ) AS
[NewString]
FROM ( SELECT 'ABC DE FG' AS [String]
UNION ALL SELECT ' ABC DE FG'
UNION ALL SELECT ' ABC DE FG'
UNION ALL SELECT 'ABCDEFG'
) A

As an update statement

UPDATE MyTable
SET [String] = STUFF ( [String], CHARINDEX(CHAR(32),[String] ), 1,
SPACE(0) )
WHERE CHARINDEX(CHAR(32),[String] ) > 0
John

"Little PussyCat" <SPAMSPAM@.NOSPAM.com> wrote in message
news:v7ibg2-thj.ln1@.tiger.sphynx...
> Hello,
> I need to be able to replace only the first occurance of a space character
> in a column.
> Reason being is the data in the column I am trying to replace seems to
> have
> umpteen space characters after each entry, so a simple replace function
> will replace all the spaces after it with what I want!
> I have thought of RTRIM to get rid of the spaces after and then replace, I
> have also thought of CHARINDEX to find the first occurance of a space and
> STUFF to replace it.
> I have done my homework on these functions!
> But I am having trouble writing such a statement,
> I've never written a query which would use more then one function on one
> column you see and I am getting confused!
> I'll tell you what I want to do in simple steps
> Replace only the first found space in a name column, but then if a name
> has
> a middle initial that will be a problem,
> Replace that with a dot.
> then concatanate '@.emailaddress;@.emailaddress2' after it
> so when SQLServer does the select it will bring back something like
> joe.bloggs@.emailaddress;emailaddress
> But I guess I'd also need joe.n.bloggs@.emailaddress;emailaddress
> The data in the column looks like this at the moment
> joe bloggs
> But I guess there may come a time when we have
> joe n bloggs, just to complicate things!
> What is your advice, and how do I write a query like this
> I have been playing around with it in Query Analyser but as I said I am
> getting confused and need some help if you don't mind
> Thanks a lot to all who reply :-)
> Regards
> Jayne|||Little PussyCat wrote:

> Hello,
> I need to be able to replace only the first occurance of a space character
> in a column.
> Reason being is the data in the column I am trying to replace seems to
> have umpteen space characters after each entry, so a simple replace
> function will replace all the spaces after it with what I want!

Its ok, I've found a way to do it.
firstly I don't want extra spaces after each name anyway so I do

Update [tablename]
SET [columnname] = RTRIM(Columnname]

Then I just do SELECT REPLACE(ColumnName,' ','.') +
'@.emailaddress;@.emailaddress'
which replaces all of the spaces in the name column and puts a dot in and
also takes into account people's names who have a middle initial! then
appends the rest of the email address in.

Thanks anyway,

Jayne

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.