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