MYSQL / PHP如何查找以相同单词开头的所有行？

Question

in my DB i have a table with brand names, and I need to clean it up since i have some duplicates like

Yadda

Yadda & Co

Yadda Engineering

which are the same brand but have been entered by mistake multiple times despite the column is unique

how to approach this? can this be done in mysql query only?

thanks

Answer 1

You can use substr

assuming that your rows begin all with the words 'Yadda' you can

 select * from my_table 
 where substr(my_column, 1, length('Yadda')) = 'Yadda');

Then if you need delete the improper rows you can (assuming that you want save the rows with 'Yadda & Co' you could

delete from my_table
where substr(substr(my_column, 1, length('Yadda')) = 'Yadda')
and my_column <> 'Yadda & Co';

or for American

delete from my_table
where substr(substr(my_column, 1, length('American')) = 'American')
and my_column not in  ('American Standard', 'American Airlines');