Escape characters in MySQL, in Ruby

Question

I have a couple escaped characters in user-entered fields that I can't figure out.

I know they are the "smart" single and double quotes, but I don't know how to search for them in mysql.

The characters in ruby, when output from Ruby look like \222, \223, \224 etc

irb> "\222".length => 1

So - do you know how to search for these in mysql? When I look in mysql, they look like '?'.

I'd like to find all records that have this character in the text field. I tried

mysql> select id from table where field LIKE '%\222%' 

but that did not work.

Some more information - after doing a mysqldump, this is how one of the characters is represented - '\\xE2\\x80\\x99'. It's the smart single quote.

Ultimately, I'm building an RTF file and the characters are coming out completely wrong, so I'm trying to replace them with 'dumb' quotes for now. I was able to do a gsub(/\222\, "'").

Thanks.

Answer 1

I don't quite understand your problem but here is some info for you:

• First, there are no escaped characters in the database. Because every character being stored as is, with no escaping.
• they don't "look ilke ?". I's just wrong terminal settings. SET NAMES query always should be executed first, to match client encoding.
• you have to determine character set and use it on every stage - in the database, in the mysql client, in ruby.
• you should distinguish ruby strings representation from character itself.
• To enter character in the mysql query, you can use char function. But in terminal only. In ruby just use the character itself.
• smart quotes looks like 2-byte encoded in the unicode. You have to determine your encoding first.