MySQL Database why do Õ come back as O when Search

Question

i have multiple special characters (Õ) in my table column When i search for Õ its showing O also in search results.

select * from table where column like '%Õ%'

i want to replace Õ with single Question in my Table. Example : its saving as below

ItÕs going to be just one of the factors that will be the cause of the resistance

so figure out which one it is and focus on that one.

update table set column = replace('Õ','?') where column like '%Õ%'; — Sloan Thrasher, Apr 08 '17 at 17:58
Did you even search for "mysql string replace" before posting your question? — Sloan Thrasher, Apr 08 '17 at 17:59
@SloanThrasher when i search it showing the O letter results as well Ex:Object,Oppostite,NÕ,NanÕ, etc.. i want to get only `Õ` these results. — Mohammad Fareed, Apr 08 '17 at 18:00
Your question is `why do Õ come back as O?` or `How can I replace Õ with O?`? — chris85, Apr 08 '17 at 18:04
See if the answer by O. Jones works for your data: http://stackoverflow.com/questions/401771/how-can-i-find-non-ascii-characters-in-mysql — Sloan Thrasher, Apr 08 '17 at 18:05
@chris85 my question is How can I replace Õ with with single quotation `'` — Mohammad Fareed, Apr 08 '17 at 18:08
@SloanThrasher first comment is pretty close to what you need, `update table set column = replace(column, 'Õ', "'") where column like '%Õ%'`. — chris85, Apr 08 '17 at 18:09
@chris85 before update i want to select the data how can i search that data — Mohammad Fareed, Apr 08 '17 at 18:11
Use a `select` in place of the `update` you can use the `replace` in the `select` and it will show you what it will do. `select column as original_data, replace(column, 'Õ', "'") as new_data from table where column like '%Õ%'` Replace `column` and `table` in all instances with your real names. — chris85, Apr 08 '17 at 18:12
@chris85, when i `select * from table where column like '%Õ%'` then its showing the results contains O instead of Õ. — Mohammad Fareed, Apr 08 '17 at 18:15
Then the question was/is `why do Õ come back as O?`... I think the collation you are using may be accent insensitive. — chris85, Apr 08 '17 at 18:16
Possible duplicate of [How to conduct an Accent Sensitive search in MySql](http://stackoverflow.com/questions/500826/how-to-conduct-an-accent-sensitive-search-in-mysql) — chris85, Apr 08 '17 at 18:19
Where did the `Õ` come from? Maybe it would be better to _prevent_ it than to _fix_ it? — Rick James, Apr 08 '17 at 19:46
Is this related? http://stackoverflow.com/questions/43299426/how-to-retrive-the-data-if-it-is-special-charecter-%c3%95 — Rick James, Apr 08 '17 at 20:07

score 2 · Accepted Answer · answered Apr 08 '17 at 19:51

If you are using utf8 currently:

mysql> SELECT REPLACE('O-o-Õ-Õ-x', 'Õ', '?') COLLATE utf8_bin;
+----------------------------------------------------+
| REPLACE('O-o-Õ-Õ-x', 'Õ', '?') COLLATE utf8_bin    |
+----------------------------------------------------+
| O-o-?-?-x                                          |
+----------------------------------------------------+

Notice how it replaced only the Õ characters.

If you are using utf8mb4, then change to COLLATE utf8mb4_bin.

Caution -- Your problem is very unusual. If you have left out some aspects of the problem, this solution may do more harm than good.

MySQL Database why do Õ come back as O when Search

1 Answers1