Mysql near duplicates/partial match/LIKE with alias

user_18181871 0
Viewed 20

My table contains some amount of near duplicates (in the "name" column). For example, record 1: abc; record 2: abc-=?; record 3: town; record 4: ?town; record 5: black^pu; record 6: black==pu, etc. They are not in a consecutive order.

Overall, there are 3 columns: dbid (primary key), name (can be associated with different internal ids), and internal id (can be associated with several names).

So, I would like to find all near duplicates (names) for starters. I am quite sure that it is not the most challenging thing to do but I can't figure out how to do it.

I was thinking about using LIKE pattern: SELECT A.name AS pl1, A.id AS n1, B.name AS pl2, B.id AS n2 FROM table1 A, table1 B WHERE A.name LIKE '%B.name%' AND A.id=B.id AND A.dbid<>B.dbid

But from my understanding in this way the clause following LIKE look for exact match of what is inside '' instead of checking the alias. Is there any way to address this issue? Any help will be much appreciated.

mysql
1 Answers

According to LIKE properties you can add % wildcard to search values into a field.

So if we have two records in the field "name" that:

Record 1: "Dan" Record 2: "Dani"

you can execute, to get only first record:

SELECT name FROM table_name WHERE name LIKE "Dan"

you can execute, to get first and second record:

SELECT name FROM table_name WHERE name LIKE "%Dan%"

you can execute, to get second one:

SELECT name FROM table_name WHERE name LIKE "%Dan&" AND NOT LIKE "Dan"
user_98726000
Related