CHARINDEX and STUFF/REPLACE

[Little PussyCat]

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

[John Bell]

Hi

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" <SP******@NOSPAM.com> wrote in message
news:v7************@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]

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