SQLite search disregarding spaces and Pinyin tones
Very interesting question!

I can't see which other way could be used in SQL by itself.

But an alternative method might be to prepare a regexp pattern, then use it with REGEXP, like this:

function tones_pattern($str) {
  for ($i = 0, $n = strlen($str); $i &lt; $n; $i++) {
    @$pattern .= $str[$i] . '([1-4] )?';
  }
  return @$pattern;
}

$results = $db-&gt;query("
SELECT CHS, PIN, DEF
FROM MAIN
WHERE CHS LIKE '%" . $q . "%'
OR PIN REGEXP '" . tones_pattern($q) . "'
OR PIN LIKE '%" . $q . "%'
");


So you get rid of what ashames you :)
But I didn't test it, and it might have performance issues. Let me know!

Note: I wrote the $pattern above according what I understand from the example you gave in your comment, i.e. each tone digit is always followed by a space. So two points:


If the above rule is true, then your own solution could already be a bit simplified into
REPLACE(REPLACE(REPLACE(REPLACE(PIN, '1 ',''),'2 ',''),'3 ',''), '4 ','')
At the opposite if the space is not always present, my $pattern must be turned from '([1-4] )?' to [1-4]? ?