Search and Replace Update-Query

[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

 

[Guest]

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");

 

[Christofer Dutz]

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");

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

 

[Larry Daugherty]

Hi Elwin,

maybe something like

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

HTH
--
-Larry-
--

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");

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

:

 

[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

 

[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!)

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");

 

[Christofer Dutz]

So write your own equivalent function.

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

Hehe ... that's exactly the function I have used now. But I still have
to go the way of using a select first. Or how can I use my own functions
from within a SQL-statement (like sored functions on other systems)?

Chris