Use LIKE to find duplicate strings in SQLite
I’ve been importing all of my separate blogs/page creators into a central Django CMS based off of SQLite. One issue has been that keywords are getting duplicated because SQLite is case sensitive.
LIKE is not case sensitive, however, so you can use that to find duplicate strings.
- SELECT GROUP_CONCAT(key2.key, ', '), COUNT(key1.id)-1 AS duplicateCount FROM keywords key1 JOIN keywords key2 ON key1.key LIKE key2.key GROUP BY key1.id HAVING duplicateCount > 0;
The table is “keywords”, the column with the keyword’s name is “key”.
- SQLite Query Language: expression at SQLite
- “SQL as understood by SQLite.”
More SQLite
- Maven: SQLite front end
- Cutedge has a SQLite3 front-end that almost works.