%like modifier

[JanTvrdik]

:+1: I need this right now.

[hrach]

Glad to see you are going to contribute :-)

[Mikulas]

:+1:

Quite coincidentally, github devs wrote this post recently: http://githubengineering.com/like-injection/

I'm not sure new modifier is a way to go though. It would be more readable to have something like

q('SELECT * FROM foo LIKE %s', $dbal->escapeLike("s%pike")); // 's\%pike'
q('SELECT * FROM foo LIKE %s', $dbal->escapeLike("s%pike") . '%'); // 's\%pike%'

or better yet

q("SELECT * FROM foo LIKE %s||'%'", $dbal->escapeLike("s%pike")); // 's\%pike%', pg concat

[hrach]

Personally, I don't like the dibi way: %~like, etc. Something like %rlike is bad bacause such operator exists too. calling $dba->escapeLike() is too complicated :(

[Mikulas]

Yeah.

So in the end this would be best?

CREATE FUNCTION escape_like(text) RETURNS text
LANGUAGE SQL IMMUTABLE STRICT AS $$
    SELECT replace(replace($1, '%', '\%'), '_', '\_');
$$;

q("SELECT * FROM foo WHERE bar LIKE escape_like(%s) || '%' LIMIT …", "s%pike");

[hrach]

what about %_like, %_like_, %like_

[Mikulas]

I don't really see difference to %~like syntax.

What if %like translated to generic %s (without escaping) and %prefix to %s + % with escaping? (Counterpart postfix match intentionally omitted). Also I don't believe there is a need for dedicated ~like~: it can be written with the like I propose, or might even be replaced with fulltext search.

[hrach]

I don't really see difference to %~like syntax.

_ is easier to write, also, it's commonly used as a placeholder, ~ isn't