Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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),'%');
share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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