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.
Remove spaces from the column and the search text:
select * from tablename
where replace(textcolumn, ' ', '') like '%' || replace('<your search string>', ' ', '') || '%'