MYSQL/PHP How to find all rows that start with the same word?

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

And you have companies "American General" and "American Standard" and you somehow want to magically handle all this right. Good luck with that one. — Drew, Oct 16 '16 at 07:19
the best solution to this problem is to avoid the duplication of brand names. U can use select option in html to list the possible brands. (A bit difficult to find and list all brands) — jophab, Oct 16 '16 at 07:25

score 1 · Answer 1 · edited May 23 '17 at 12:16

My offer first create FullText index for search column. After with full text score you can determine similarity of strings. Delete them unless which have greater score and greate than threshold.

DELETE FROM table_name 
WHERE
    id <> (SELECT id FROM table_name ORDER BY MATCH (title) AGAINST ('Yada' IN NATURAL LANGUAGE MODE) DESC LIMIT 0,1) AND
    MATCH (title) AGAINST ('Yada' IN NATURAL LANGUAGE MODE) < threshold

Also instead off Fulltext you can use levenshtein function in this answer (with same logic sql query) how to compute similarity between two strings in MYSQL

ScaisEdge · Answer 2 · 2016-10-16T07:24:53.220

0

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');

edited Oct 16 '16 at 07:24

answered Oct 16 '16 at 07:11

ScaisEdge

131,976
10
91
107

1

his question is not about Yadda – Drew Oct 16 '16 at 07:14
@Drew . the title say .. rows that start with the same word .. anyway .. answer updated . with a brief explaination – ScaisEdge Oct 16 '16 at 07:16
yes, that is something i thought of using. but how to iterate for each rows (i have thousands).. shoudl i use a for loop in php? – Francesco Oct 16 '16 at 07:17
like I said, it is not about Yadda :p – Drew Oct 16 '16 at 07:17
which is the logic for save a row and delete the others? – ScaisEdge Oct 16 '16 at 07:18
@Francesco . i have update the answer with a suggestion for delete ... hope is useful – ScaisEdge Oct 16 '16 at 07:22

score 0 · Answer 3 · answered Oct 16 '16 at 07:26

0

SELECT Name FROM Table WHERE Name REGEXP '^[Y].*$'

In this REGEXP stands for regular expression

and

this is for T-SQL

SELECT Name FROM Table WHERE Name LIKE '[Y]%'

answered Oct 16 '16 at 07:26

Sheikh Noman Mehmood

61
1
2
9

MYSQL/PHP How to find all rows that start with the same word?

3 Answers3