SQLite search disregarding spaces and Pinyin tones I have a column where, pretty much, each entry has numbers (specifically #1-4) and spaces (' '). I wanted to make a query that could avoid having to know these numbers and spaces.

$results = $db-&gt;query( "SELECT CHS, PIN, DEF FROM MAIN WHERE CHS LIKE '%".$q."%' OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PIN, '1',''),'2',''),'3',''), '4',''),' ','') LIKE '%".$q."%' OR PIN LIKE '%".$q."%'");


The part that I'm worried (read:ashamed) about is:


REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PIN, '1',''),'2',''),'3',''), '4',''),' ','')



Is this a super bad way to code?
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]? ?