Search and Replace Update-Query

C

Christofer Dutz

Hi,

I am working on a small DB-application and have run into the folowing
problem:
I want to replace any ocurences of a string by another. This would be no
problem, if i wanted to replace the entire database-field. My first
attempt was to Select all records using the LIKE keyword and then update
every entry one after the other. I think there must be a more performant
way to do this. I managed to create an update statement, that updates
one and only one occurence of a string, but failes if the string
contains two or more of these string-fragments.

Anny Ideas?

Chris
 
E

elwin

The following SQL update query scans all values in 'myField' for one or more
occurances of 'green' and replaces it with the word 'blue'.

UPDATE Table1 SET Table1.myField = Replace([myField],"green","blue");
 
C

Christofer Dutz

elwin said:
The following SQL update query scans all values in 'myField' for one or more
occurances of 'green' and replaces it with the word 'blue'.

UPDATE Table1 SET Table1.myField = Replace([myField],"green","blue");
Click to expand...
That's funny ... I thought i saw that it only replaced on occurence
(would have made "green blue sky" from "blue blue sky" ... well ... Have
to try that again ...
Thanks anyway

Chris
 
L

Larry Daugherty

Hi Elwin,

maybe something like

UPDATE Table1 SET Table1.myField = "*" & "Blue" & "*"
WHERE (((Table1.MyField) Like "*" & "Green" & "*"));

HTH
--
-Larry-
--

Christofer Dutz said:
elwin said:
The following SQL update query scans all values in 'myField' for one or more
occurances of 'green' and replaces it with the word 'blue'.

UPDATE Table1 SET Table1.myField = Replace([myField],"green","blue");
Click to expand...
That's funny ... I thought i saw that it only replaced on occurence
(would have made "green blue sky" from "blue blue sky" ... well ... Have
to try that again ...
Thanks anyway

Chris
:
Click to expand...
Click to expand...
 
C

Christofer Dutz

This worked fine ... untill I converted my Access 2002 DB to Access97.
Unfortunately Access97 doesn't seem to support the Replace Function :(

Chris
 
D

Douglas J. Steele

So write your own equivalent function.

One approach is shown at http://www.mvps.org/access/strings/str0004.htm at
"The Access Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Christofer Dutz said:
This worked fine ... untill I converted my Access 2002 DB to Access97.
Unfortunately Access97 doesn't seem to support the Replace Function :(

Chris

The following SQL update query scans all values in 'myField' for one or
more occurances of 'green' and replaces it with the word 'blue'.

UPDATE Table1 SET Table1.myField = Replace([myField],"green","blue");
Click to expand...
Click to expand...
 
Top