regexsqlite

SQLite Pattern Matching with Extra Character


My database contains these rows:

DuPage

Saint John

What queries could I use that would match people entering either 'Du Page' or 'SaintJohn': in other words: adding an extra character (at any position) that shouldn't be there, or removing a character (at any position) that should be there?

The first example has a possible workaround: I could just remove the space character from the 'Du Page' input before searching the table, but I cannot do that with the second example unless there was some way of saying 'match 'SaintJohn' with the database text that has had all spaces removed', or alternatively 'match a database row that has every letter in 'SaintJohn' somewhere in the row.


Solution

  • Remove spaces from the column and the search text:

    select * from tablename
    where replace(textcolumn, ' ', '') like '%' || replace('<your search string>', ' ', '') || '%'