Doing a partial replace of a field

Andy
You should be able to do this using the REPLACE function.
ie.
SELECT REPLACE('have a nice day', ' ', ' ')
Returns:
have a nice day
- Peter Ward
WARDY IT Solutions
"Andy" wrote:

> How could I do a replace of " " to " " in a string were the string is a
> field and the values are variable. I would like to use Enterprise Manager
or
> a stored proc.
> IE. Node.Nodename is equal to "have a nice day" I want to replace the "
> " with " " after nice.Not just a sentence. How do you do it for a field in a database table. I
would like to do it in Enterprise Manager Query or Query Builder. There are
also allot of sentences with varying text.
"P. Ward" wrote:
> Andy
> You should be able to do this using the REPLACE function.
> ie.
> SELECT REPLACE('have a nice day', ' ', ' ')
> Returns:
> have a nice day
>
> - Peter Ward
> WARDY IT Solutions
>
> "Andy" wrote:
>|||update table
set field = replace( field , 'twospaces' , 'onespace' );
where
field like '%twospaces%'
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:4183FA77-4739-4526-94EB-C2664A4CC2B6@.microsoft.com...
> Not just a sentence. How do you do it for a field in a database table. I
> would like to do it in Enterprise Manager Query or Query Builder. There
are
> also allot of sentences with varying text.
> "P. Ward" wrote:
>
a
Manager or
the "|||Although, saying that LIKE may ignore whitespace.
You can use where (CHARINDEX(field , 'twospaces')>0) - but this will not use
any indexing and will probably be slower.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:434fb7dc$0$136$7b0f0fd3@.mistral.news.newnet.co.uk...
> update table
> set field = replace( field , 'twospaces' , 'onespace' );
> where
> field like '%twospaces%'
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:4183FA77-4739-4526-94EB-C2664A4CC2B6@.microsoft.com...
I
> are
is
> a
> Manager or
> the "
>|||I tried this in query analyzer:
UPDATE dbo.MA_Node
SET NodeName = replace(NodeName, ' ', ' ');
WHERE (CHARINDEX(NodeName, ' ')>0)
and got this:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.
same for
"Rebecca York" wrote:

> update table
> set field = replace( field , 'twospaces' , 'onespace' );
> where
> field like '%twospaces%'
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:4183FA77-4739-4526-94EB-C2664A4CC2B6@.microsoft.com...
> are
> a
> Manager or
> the "
>
>|||Try removing the semi-colon at the end of the second line. That is a typo
(it signifies the end of a statement).
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:857EBD97-E2B3-4195-BEC1-7245F84BD3D2@.microsoft.com...
>I tried this in query analyzer:
> UPDATE dbo.MA_Node
> SET NodeName = replace(NodeName, ' ', ' ');
> WHERE (CHARINDEX(NodeName, ' ')>0)
> and got this:
> Server: Msg 156, Level 15, State 1, Line 3
> Incorrect syntax near the keyword 'WHERE'.
> same for
> "Rebecca York" wrote:
>|||On Fri, 14 Oct 2005 15:26:42 +0100, "Rebecca York" <rebecca.york {at}
2ndbyte.com> wrote:

>Although, saying that LIKE may ignore whitespace.
Hi Rebecca,
I don't know who told you that, but he/she has misinformed you:
CREATE TABLE Test
(a varchar(20) NOT NULL)
INSERT INTO Test (a)
SELECT 'One space'
UNION
SELECT 'Two spaces'
UNION
SELECT 'Three spaces'
go
SELECT a
FROM Test
WHERE a LIKE '% %'
go
DROP TABLE Test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)