CODEWITHSUNDEEP
mysql
lyborko
lyborko

Reputation: 2619

How to use UPDATE in MySQL with string containing escape characters

please look here:

    UPDATE cars_tbl 
SET description = '{\rtf1'
WHERE (ID=1)

Description field is "blob", where my RTF document is to be stored.

When I check updated data I always find 

{
tf1

\r simply disapears. I tried to find solution on the web, but no success. My rtf files are corrupted on many places, because the escape characters used in the string are substituted. How to suppress this substitution and update field with string as is?

Thanx for advice

Lyborko

Upvotes: 1

Views: 4452

Answers (2)

william.eyidi
william.eyidi

Reputation: 2365

You can achieve this with a more generic approach

use of QUOTE() in mysql

MySQL QUOTE() produces a string which is a properly escaped data value in an SQL statement, out of an user supplied string as argument.

The function achieve this by enclosing the string with single quotes, and by preceding each single quote, backslash, ASCII NUL and control-Z with a backslash.

example

UPDATE cars_tbl SET description = QUOTE('{\rtf1') WHERE (ID=1)

UPDATE

to escape your RTF you can also just use REPLACE this way all your \ will become \\

Example

UPDATE cars_tbl SET description = REPLACE('{\rtf1', '\', '\\') WHERE (ID=1)

Upvotes: 0

Arth
Arth

Reputation: 13110

Backslash is an escape character, so to keep it you need a double backslash:

UPDATE cars_tbl 
SET description = '{\\rtf1'
WHERE (ID=1)

As an aside \r is a carriage return.. and it hasn't disappeared in your data; it is responsible for tf1 appearing on the line below the {.

Upvotes: 6

Related Questions