Search code examples
mysqlsqlpostgresqlpattern-matchingprefix

? LIKE (column || '%')

Can I have a condition of something like this:

SELECT * FROM table WHERE ? LIKE (column || '%')

Where the ? is a string parameter value. For example, these parameter value ? should return true when column is equal to /admin/products

/admin/products/1
/admin/products/new
/admin/products/1/edit

Is this possible?

Update: Added test case.

Basically, the where clause would render like this:

1.  ? LIKE (column || '%')
2.  '/admin/products/1' like ('/admin/products' || %)
3.  '/admin/products/1' like ('/admin/products%')

But it always return false for me.

These queries works fine though:

column = '/admin/products' --returns true
column = '/admin/products/1' --returns false
column LIKE '/admin/prod%' --returns true

The problem arises when I put the parameter ? before the LIKE clause. Is it not allowed?
If it's not, are there any workarounds for this?

Solution

  • The query:

    SELECT * FROM table WHERE ? LIKE (col || '%');

    can be rewritten as (Postgres and MySQL):

    SELECT * FROM table WHERE col = left(?, length(col));

    As commented, the first form should work as well. It can be tricky, though, because characters with special meaning for LIKE (at least _%\) in the column would have to be escaped. If you want it to work with both MySQL and Postgres, you'll have to observe special characters in both implementations. So the 2nd form is much less error-prone on principal.

    Performance

    Neither of these queries can use an index on col, both are not sargable. The problem can be re-assessed as finding all possible prefixes to the given search pattern ?, which can be optimized in a similar fashion like in this related answer (for Postgres) on dba.SE: