CODEWITHSUNDEEP
mysql
Angry Beaver
Angry Beaver

Reputation: 465

MySQL replace QUOTE

I need to replace &quote; in a string. I tried to do this:

SET `title` = REPLACE( `title`, '"', '' )

but it gives me a parsing error.

This is the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''&quot)' at line 1

Server version: 5.5.57-cll - MySQL Community Server (GPL)

#1064

How do I do this?

Upvotes: 0

Views: 455

Answers (4)

Krzysztof Gwizdała
Krzysztof Gwizdała

Reputation: 11

The semicolon ; in " word is the reason. Change your syntax

from

SET `title` = REPLACE( `title`, '"', '' );

to

SET `title` = REPLACE( `title`, '"', '' )#

and before you click Run change separator from ; to #

Look sample here

Upvotes: 1

Shivam Arora
Shivam Arora

Reputation: 364

This is simple approach but it replaces all the " in string.

 UPDATE dummy_tab SET metatitle =REPLACE(metatitle,'/"','') WHERE metatitle LIKE '%"'

Table Before Update

Table After Update

Upvotes: 0

DarbyM
DarbyM

Reputation: 1203

If I'm to read your error 100% as written... you have the word quote spelled wrong some where.

 ...for the right syntax to use near ''&quot)'

notice in your error it shows no "e" There for your replace statement would also NOT catch this.

Or more closely looking at the image you posted... you are replacing

&quote(semicolon)

with

''

But the error says it is finding the string

&quote)

somewhere in your query... Which would seem to be invalid.

Search your code for

&quot)

Upvotes: 0

Mr.Turtle
Mr.Turtle

Reputation: 3090

Your query should work, but it seems like the error is from another query. Anyhow:

Try this:

UPDATE tbl_name 
SET 
    field_name = REPLACE(field_name,
        string_to_find,
        string_to_replace)
WHERE
    conditions;

Example:

UPDATE bbb_sefurls 
SET 
    metatitle = REPLACE(metatitle,
        '&quote;',
        '');

No need for a condition

Upvotes: 0

Related Questions