0

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.

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),'%');
| improve this answer | |
  • 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. – Hitesh Chouhan Jul 23 '13 at 7:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.