Doing Find and Replace

[Gangrel]

I would like to be able to do a find and replace on a text field in a database table automatically. In Excel, there appears to be a macro where you can do this, but I don't see the same thing in Access?

I'm not replacing the whole field, just a single character to "clean" my data. Any suggestions?

 

[hwkranger]

Try using an update statement.

update tblname
set fldname = value
where
<criteria>

this can be altered to

update tblname
set fldname = replace(fldname, "oldval", "newvale")
where
<criteria>

Or -- if you really wanted to try it the hard way

Public Function ReplaceChar(val as string)
dim rst as new adodb.recordest
rst.open "Select Statement for your data here", yourconnection...
do while not rst.eof
  <loop through characters here and replace>
loop
End function

That's my two cents.


Randall Vollen
National City Bank Corp.

 

[PHV]

Have a look to the Replace function (ac2k or above)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gangrel]

Randell, does the replace function work for one character, or the whole field?

update tblname
set fldname = replace(fldname, "oldval", "newvale")
where
<criteria>

almost looks to me like it will wipe out the entire field. Or maybe I'm just thinking wrong.....

 

[hwkranger]

Update does replace the entire field.... but..

example:

Replace("Hello To all my friends", "friends", "Enemies")

returns

"Hello to all my Enemies"

Using update you are 'replacing' the entire field. with 'replace' you are replacing the character you want to remove.



Randall Vollen
National City Bank Corp.

 

[PHV]

You may also consider the Left, InStr and Mid functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gangrel]

Ok, I've tried the following and it doesn't work:

UPDATE TMSG
SET MSG_TXT_FRC = (REPLACE(MSG_TXT_FRC,"BN","dog"));


I don't have a WHERE clause because I want to go through every row in the table. When I try this query, it tells me it's going to change 4701 rows (my table), but then when I check it after it runs, there are no changes.

Am I screwing up somewhere?

 

[rubbernilly]

And if you remove the outside parens?

If you are doing this through code and are building the SQL statement as a string, you can put the Replace() function outside of the quotes to have its return value applied to the string rather than the function itself.

 

[Gangrel]

I have tried both with and without the outside parenthesis.

And I have just created a query in Access, and am running it.

I am wondering if I"m simply confusing Access and it's trying to set all my TXT_MSG_FRC field to a string of "REPLACE(etc etc...")

 

[PHV]

And this ?
UPDATE TMSG
SET MSG_TXT_FRC = Replace([MSG_TXT_FRC], 'BN', 'dog')
WHERE MSG_TXT_FRC Like '*BN*'

And then click the exclamation button (!).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hwkranger]

As a general statement -- the where clause doesn't really matter if he's doing all.

replace only replaces if it exists -- it's perhaps not finding the string to replace?

Randall Vollen
National City Bank Corp.

 

[PHV]

the where clause doesn't really matter
It does matter for the row count in the alert message box.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hwkranger]

True -- for the row count -- but for the replace it doesn't matter.. It's doing work that isn't neccessary, but if he's experiencing no updates then it's a problem with the replacement.

I would (as you have) suggest using a where clause -- due to performance issues..

Randall Vollen
National City Bank Corp.

 

[PHV]

To avoid unexpected side effect I suggest to always use a WHERE clause in an UPDATE or DELETE instruction ...

 

[Gangrel]

Ok, I got it working now. Thanks a lot for the help guys. If I could send a beer over the 'net, I'd buy a round!

 

[PHV]

Gangrel, to send beer like this
type this [ignore][/ignore]

 

[Gangrel]

Sorry to ressurect an old thread, but I have recently created a few more of these and have some more questions.

I need to replace some apostrophe characters that are unique to French keyboards. When I try this:

UPDATE TMSG
SET MSG_TXT_FRC = Replace([MSG_TXT_FRC], ''(the french one', ''')
WHERE MSG_TXT_FRC Like '*'*'

Access doesn't like my WHERE clause as it thinks the middle apostrophe is the end of the line. Is there a trick that I can use to be able to have the apostrophe used in my WHERE clause? I remember in VB I used to have to do something along the lines of

 string = "then he said """that's incredible"""

in order to get quotes to show up in strings, but that doesn't seem to work for me in Access

 

[PHV]

UPDATE TMSG 
SET MSG_TXT_FRC = Replace(MSG_TXT_FRC, "'", "''")
WHERE MSG_TXT_FRC Like "*'*" AND MSG_TXT_FRC Not Like "*''*"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gangrel]

Thanks alot PHV!