mysql search special characters

Use following query to escape the special character:

SELECT
  products.product_name,
  products.product_id
FROM
  products
WHERE
  products.product_name LIKE '%\è%'

Select allOpen in new window

I just tried it. It doesn't work. Any other suggestions?

try using:

WHERE
products.product_name LIKE _utf8 '%\è%'


I only used _utf8 as my character set. You should change that to the character set that supports the special character you wish to select.

Sorry, when I copied "products.product_name LIKE '%\è%' ", I meant to copy it from your original post which is "products.product_name LIKE '%è%'"  (without the \). That's the one to use.

Use the character set notation in your ORIGINAL code.
"

i tried it and its working fine with out any special characters. could you verify the database charset and os charset that supports french character? table charset could be identified through 'show create table <tablename>'.

Please answer the following questions:

1. What is the character set used by the table in question?

2. Do you actually have any special characters in your tables?

3. How did you input them, are they on your keyboard?

I'd see what I can find out as I await your response.

You can also try:

WHERE
  products.product_name LIKE '%char(138)%'


If that works, then get the list of ASCII codes to use from http://www.asciitable.com/

just replace the special character with with its acsii code using the syntax char(asciicode).

Hi msk_apk,

This is what I have when I use this command:
SHOW VARIABLES LIKE '%char%';


The output for:
SHOW VARIABLES LIKE '%coll%'

 
---------
Hi cedlinx,

I tried all the commands that you suggested.

1. What is the character set used by the table in question?
[Victor's response] please see output above.

2. Do you actually have any special characters in your tables?
[Victor's response] the only special characters are like the French characters.

3. How did you input them, are they on your keyboard?
[Victor's response] No, I think they were copied and pasted from MS Word.

--------------

I tried this:

WHERE
  products.product_name LIKE '%char(138)%'

There is only one record that has this special character in French è out of 1000 records. That query is slow.

It seems like from this post:
http://stackoverflow.com/questions/5408188/mysql-search-like-with-optional-french-characters

that people can search using french characters. I'm wondering if there are any character set issues that I overlooked or perhaps when I copy and paste from MS Word from their special characters then it's different then if I had a French keyboard.

Sorry, forgot to add that this:
WHERE
  products.product_name LIKE '%char(138)%'

doesn't work.

I also just tried changing the keyboard to canada/french but I don't think I can get that è with my english keyboard.

I very nearly missed this.  '%char(138)%' won't work.  That would (I assume) look for the literal string "char(138)".

 Maybe try something like this?

WHERE
  products.product_name LIKE CONCAT('%', char(138), '%')


 Disclaimer:
 I haven't use MySQL, so I may be missing something.

 James

Hi Bhavesh,

Okay, I'll test it out. Thanks. I'll let you know and post back shortly.

Victor

Thank you. that works. =)