• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

replace () not working

New Here ,
Sep 09, 2008 Sep 09, 2008

Copy link to clipboard

Copied

I have the simple code

UPDATE blog_comments
SET comment=replace(comment,'#phrase#','*****')

Where #phrase# is a specific word.

The results are not working like I thought.

If #phrase# were 'drink'

it replaces the word drinking with *****ing. I don't want it to replace the first part of a word and it is doing that. I only want it to replace the exact word drink with *****.

It also does the the same with the word drinks. It give me *****s.

How can I have it only replace the exact string without seeing a portion of a word?
TOPICS
Advanced techniques

Views

672

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 09, 2008 Sep 09, 2008

Copy link to clipboard

Copied

A crude way may be to change your replace to have it search for "#phrase# " (space added after the second #). To be complete you may have to also do
"#phrase#. "
"#phrase#, "

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 09, 2008 Sep 09, 2008

Copy link to clipboard

Copied

quote:

A crude way may be to change your replace to have it search for "#phrase# " (space added after the second #). To be complete you may have to also do
"#phrase#. "
"#phrase#, "

This doesn't solve the issue.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 09, 2008 Sep 09, 2008

Copy link to clipboard

Copied

You may also want to look into using rereplace, which allows you to use
regular expressions that can give you more control over exactly what you
want to find.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 09, 2008 Sep 09, 2008

Copy link to clipboard

Copied

Ian,
Is it okay to use rereplace within my MySQL query statement instead of replace()?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 09, 2008 Sep 09, 2008

Copy link to clipboard

Copied

Phox68 wrote:
> Ian,
> Is it okay to use rereplace within my MySQL query statement instead of replace()?

I would think so, reReplace returns the same thing that replace does, a
modified string. You just need to use Regular Expression syntax which
is a whole 'nother 'language' if you have never worked with it before.

A quick, dirty and untested idea would be something like this.

SET comment = '#replace(comment, phrase & "[^A-Za-z]", '******','ALL')#'

OR replace every instance of phrase that is not followed by a letter
with stars.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 09, 2008 Sep 09, 2008

Copy link to clipboard

Copied

This wiped out my entire table of records by replacing every record with the phrase.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 09, 2008 Sep 09, 2008

Copy link to clipboard

Copied

LATEST
> This wiped out my entire table of records by replacing every record with the phrase.

The original query you quoted didn't have a WHERE clause.

So you pretty much told the DB to do that :-(

--
Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation