MySQL Replace function throwing error

Question

I am trying to update ^G (Ctrl G) in a table, below is scenario-

CREATE TABLET TEST(COLA INT,DESCRIPTION VARCHAR(20));

I have crtl G in my data for description field, so to replace it with a ,円I am running following command-

UPDATE TEST 
SET DESCRIPTION=REPLACE(DESCRIPTION,CHAR(92),CHAR(7)) 
WHERE DESCRIPTION like '%' CHAR(07) '%';

It runs fine,converts the ^G into \ but it gives a warning as well

TRUNCATED INCORRECT INTEGER VALUE '%'

Can somebody please help me understand this warning.

Answer 1

That's odd, When I run your statements on 5.5 I get a syntax error:

mysql> CREATE TABLE TEST(COLA INT,DESCRIPTION VARCHAR(20));
Query OK, 0 rows affected (0.02 sec)
mysql> UPDATE TEST 
 -> SET DESCRIPTION=REPLACE(DESCRIPTION,CHAR(92),CHAR(7)) 
 -> WHERE DESCRIPTION like '%' CHAR(07) '%';
ERROR 1064 (42000): 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 'CHAR(07) '%'' at line 3

Anyway, the proper way to do what you want to achieve would be concatenating '%' and the \G, that is:

UPDATE TEST 
SET DESCRIPTION=REPLACE(DESCRIPTION,CHAR(92),CHAR(7)) 
WHERE DESCRIPTION like CONCAT('%',CHAR(07),'%');

• Hitesh Chouhan: Thanks a lot @redguy it worked for me and i also figured out why was the above one worked for me. Because MySQL was taking the first '%' and matching everything.