patternphpMinor

SQLite search disregarding spaces and Pinyin tones

Submitted by: @import:stackexchange-codereview·Mar 10, 2026·

Viewed 0 times

tonessearchsqlitepinyinspacesanddisregarding

Problem

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->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?

Solution

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 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]? ?

Code Snippets

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

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

Context

StackExchange Code Review Q#113044, answer score: 2

Revisions (0)

No revisions yet.