? LIKE (column || '%')

Question

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?

AFAIK Table and Column names cannot be replaced by parameters. — Alex Szabo, Nov 18 '14 at 08:14
This sounds like a front end/ implementation issue, not a database issue. — Scotch, Nov 18 '14 at 08:15
@Scotch We were actually implementing a user role on our system. We put URL of base pages on the database and validate it in our base controller of the administration site so that we wouldn't have any redundant codes. — John Isaiah Carmona, Nov 18 '14 at 08:17
In SQL `+` is the operator to add numbers, it's not for character concatenation (the operator to concatenate strings is `||`) — , Nov 18 '14 at 08:18
@a_horse_with_no_name Noted, fixed the question regarding SQL string concatenation symbol. — John Isaiah Carmona, Nov 18 '14 at 08:19
It should work fine, but maybe you'll need to use quotes explicitly: '?' LIKE column || '%' — Multisync, Nov 18 '14 at 08:20
@Multisync Not working though. The query renders like this `'admin/products/1/edit' like ('admin/products' || '%')`. — John Isaiah Carmona, Nov 18 '14 at 08:23
Can you show a complete test case? This looks fine as written. — Craig Ringer, Nov 18 '14 at 08:30

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

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:

Algorithm for finding the longest prefix

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 18 '14 at 08:47

Erwin Brandstetter

605,456
145
1,078
1,228

1

Thank you very much! It also works on MySQL. You save me a day of work. – John Isaiah Carmona Nov 18 '14 at 08:50
It still doesn't explain *why* the original issue arose, but it'll sure do the job. – Craig Ringer Nov 18 '14 at 08:52
The first form should work as well. It can be tricky, though, because special characters like `_%\\` *in the column* would have to be escaped. So the 2nd form is much less error-prone. – Erwin Brandstetter Nov 18 '14 at 08:53

score 1 · Answer 2 · answered Nov 18 '14 at 09:38

1

Replacing

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

by

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

works for me.

Maybe || is used as logical or-operation instead of concatenation.

answered Nov 18 '14 at 09:38

Julian Ladisch

1,367
9
10

Thanks! Will it work on postgre too? Our production database is actualy on postgre, and our dev environment is on MySQL. – John Isaiah Carmona Nov 18 '14 at 09:42
@JohnIsaiahCarmona: I would strongly suggest to use the same RDBMS for dev & production, or you'll keep running into "surprising" problems. There is also a [`concat()` function in Postgres](http://www.postgresql.org/docs/current/interactive/functions-string.html#FUNCTIONS-STRING-OTHER) but `||` is the standard-SQL concatenation operator. And [the name is not "postgre"](https://wiki.postgresql.org/wiki/Identity_Guidelines). – Erwin Brandstetter Nov 18 '14 at 10:08
@ErwinBrandstetter They actually state that *All other names are incorrect, especially Postgre* in their documentation, lol, sorry. My colleagues was also using PostgreSQL but I need a better IDE for my database (on-table editing and stuff) which I can't find for PostgreSQL (am using MySQL Workbench). But I personally think dolphins and elephants are the same ;) – John Isaiah Carmona Nov 18 '14 at 10:19
1

@John PostgreSQL since 9.1 also has a `concat` function, working example: http://sqlfiddle.com/#!15/07da7/1 – Julian Ladisch Nov 18 '14 at 10:22

score 0 · Answer 3 · answered Nov 18 '14 at 08:46

0

Works for me.

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

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

returns true.

Your test case doesn't appear to accurately reflect the actual issue at hand.

answered Nov 18 '14 at 08:46

Craig Ringer

307,061
76
688
778

Sorry that was just a typo. :p Already fixed. Thanks for pointing that out. – John Isaiah Carmona Nov 18 '14 at 08:48
@JohnIsaiahCarmona I'm still convinced your real issue is elsewhere and the testcase doesn't really reflect what you're doing. It makes no sense that `select '/admin/products/1' like ('/admin/products%')` could return false for you. – Craig Ringer Nov 18 '14 at 08:51
I actually checked the generated SQL query by debugging and it is `'/admin/products/1' like (url || '%')` but it really returns false. I think the problem is on the implementation of the `like` condition not allowing the table name after it. But running your query on MySQL returns true. Thanks for helping though! I really appreciate it! – John Isaiah Carmona Nov 18 '14 at 08:55

? LIKE (column || '%')

3 Answers3

Performance

Linked