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
- Goodreads: What books did I read last week and last month?
- I occasionally want to look in Goodreads for what I read last month or last week, and that currently means sorting by date read and counting down to the beginning and end of the period in question. This Python script will do that search on an exported Goodreads csv file.
- Maven: SQLite front end
- Cutedge has a SQLite3 front-end that almost works.