• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 849
  • Last Modified:

mysql search special characters

Hi,

I'm trying to search for special characters like:
SELECT
  products.product_name,
  products.product_id
FROM
  products
WHERE
  products.product_name LIKE '%è%'

Open in new window


But the returned data is coming back with anything with a regular 'e' and not the special character 'e'. How do I use a mysql statement to do special searches? The other character I'd like to search for is: î
which is the special french character as html entity î

Thank you,
vkimura
0
Victor Kimura
Asked:
Victor Kimura
  • 5
  • 4
  • 2
  • +3
2 Solutions
 
Jagadishwor DulalBraces MediaCommented:
Use following query to escape the special character:

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

Open in new window

0
 
Victor KimuraAuthor Commented:
I just tried it. It doesn't work. Any other suggestions?
0
 
Cedric Obinna A.Full Stack DeveloperCommented:
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.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Cedric Obinna A.Full Stack DeveloperCommented:
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.
"
0
 
msk_apkCommented:
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>'.
0
 
Cedric Obinna A.Full Stack DeveloperCommented:
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.
0
 
Cedric Obinna A.Full Stack DeveloperCommented:
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).
0
 
Victor KimuraAuthor Commented:
Hi msk_apk,

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

show var char
The output for:
SHOW VARIABLES LIKE '%coll%'

 show var 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.
0
 
Victor KimuraAuthor Commented:
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.

0
 
James0628Commented:
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
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Try with this.

- Bhavesh
SELECT
  products.product_name,
  products.product_id
FROM
  products
WHERE
  products.product_name LIKE '%è%' COLLATE utf8_bin;

Open in new window

0
 
Bhavesh ShahLead AnalysistCommented:
0
 
Victor KimuraAuthor Commented:
Hi Bhavesh,

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

Victor
0
 
Victor KimuraAuthor Commented:
Thank you. that works. =)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now