finding bad characters in sql table

Question

I have a table in my database where some of the data for the column "customer name" has been updated as invalid or Spanish characters as below.

Ana MartÃnez
Â Ann  Harms
Â Don  Bergh
Maria Muñoz

I want to find the list of customername which has these kind of invalid or Spanish characters. Can we write a query to identify these?if yes, please assist.

Did these characters appear due to the database columns not being in UTF-8 formats? If so, once solved, I suggest you alter your relevant tables to use utf8_general_ci. — Martin, Sep 27 '18 at 07:22
https://gist.github.com/jgdoncel/bc20b39b8cd612c4a26dfcaf3bb14dd8 this function remove accents but unfortunately in mysql code — dwir182, Sep 27 '18 at 07:24
actually I don't have write access to my database.but I wanted to find out the records which has these characters — unnikrishnan, Sep 27 '18 at 07:25

score 0 · Answer 1 · answered Sep 27 '18 at 07:17

0

I think you should just list all names where unwanted charracter is inside. For this you would need all "bad" characters

select * from yourtable where customer_name like '%Ã%' or customer_name like '%ñ%'

answered Sep 27 '18 at 07:17

Kusy

240
1
12

Think he was just using those as examples. The scale might be much larger than just those 2 characters. It could be quite comprehensive. – Martin Sep 27 '18 at 07:20
hmm, yeah, ok but anyways the list of special characters shouldnt be that long as these as Spanish characters only (what i read in his question ) – Kusy Sep 27 '18 at 07:23
when I use this query I am not getting the examples I have mentioned in my question. the result has no rows – unnikrishnan Sep 27 '18 at 07:23
did you correctly update the column names and tablename? – Kusy Sep 27 '18 at 07:24
Worth noting that if you have multiple special characters, it might be more convenient to make a *regexp* to handle the logic. It'll be a lot faster too. – Martin Sep 27 '18 at 07:34

finding bad characters in sql table

1 Answers1