Clever Geek Handbook

? LIKE (column || '%')

Can I have a condition something like this:

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

Where ?

is the value of the string parameter. For example, this parameter?

value should return true if the column is/admin/products 

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

Is it possible?

Update: Added test case.

Basically the where clause would look like this:

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

But it always returns false for me.

These queries work fine:

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

The problem arises when I put a parameter ?

in front of the sentence LIKE

. Is not allowed?
If it is not, are there any workarounds for this?

+3


source to share


3 answers


Request:

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

can be rewritten as (Postgres and MySQL):

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


As noted, the first form should work. This can be tricky because characters with a special meaning for LIKE

(at least _%\

) in the column must be escaped. If you want it to work with both MySQL and Postgres, you need special characters in both implementations. Thus, the second form is much less error prone in principle.

Performance

None of these queries can use an index on col

, both are not sargable . The problem could be re-evaluated as finding all possible prefixes for a given search pattern ?

, which can be optimized in a similar way as in this linked answer (for Postgres) on dba.SE:

+2


source


Replacement

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

by



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

works for me.

Maybe || used as a boolean operation or an operation instead of concatenation.

+1


source


Works for me.

PREPARE withparam(text) AS SELECT $1 LIKE ('/admin/products' || '%');

EXECUTE withparam('/admin/products/1');

returns true.

Your test case doesn't match the exact actual question.

0


source






More articles:


All Articles