Update Query (searching with wildcards and replacing)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a table with a column which contains
BUSPEW.JAVA.ScorecardEnvironmentSorting.BUSPEWSCORECARDENVIRONMENTSORTINGENVCOM
in different forms around 65,000 times and as each one is different i am finding it hard to figure out an update command that will go through this whole column and all these values and replace all the . (full stops) with the "tab" character.
I have only been able to come up with

UPDATE CORE2004_TERMINOLOGYLISTFR
SET PATH = ' '
WHERE PATH='.';

so far and ive tried putting * either side of the .
Could any one help me out please?!
I've been trying for so long!
Thankyou,
Alex
 
Alex1984 said:
Hi,
I have a table with a column which contains
BUSPEW.JAVA.ScorecardEnvironmentSorting.BUSPEWSCORECARDENVIRONMENTSORTINGENVCOM
in different forms around 65,000 times and as each one is different i am finding it
Click to expand...
hard to figure out an update command that will go through this whole column and all
these values and replace all the . (full stops) with the "tab" character.
I have only been able to come up with

UPDATE CORE2004_TERMINOLOGYLISTFR
SET PATH = ' '
WHERE PATH='.';

so far and ive tried putting * either side of the .
Could any one help me out please?!
I've been trying for so long!
Click to expand...
Hi Alex,

What version of Access?

In Access 2000, you need to create a "wrapper function"
around the function "Replace" to use in a query.

Save the following (provided by Duane) in a module
(make sure name of module is *not* the same
as name of your function, i.e., do not name your
module "ReplaceIt"):

Public Function ReplaceIt(pstrBigText As String, _
pstrFind As String, _
pstrReplacement As String) As String
ReplaceIt = Replace(pstrBigText, pstrFind, pstrReplacement,1,-1,1)
End Function

Then you can use in an update query:
(if you want to replace "." with " ")

UPDATE CORE2004_TERMINOLOGYLISTFR
SET PATH = ReplaceIt([Path],"."," ")
WHERE InStr(1,[PATH],".",1)>0;

if you want to replace "." with "tab"

UPDATE CORE2004_TERMINOLOGYLISTFR
SET PATH = ReplaceIt([Path],".",Chr(9))
WHERE InStr(1,[PATH],".",1)>0;

Good luck,

Gary Walter
 
Back
Top