orm DDC-3311: Add ESCAPE clause support after LIKE pattern, in Expr class

Jira issue originally created by user Vicente69:

Actually, it is not possible to specify an ESCAPE character in a LIKE clause. The Expr::like method only takes two arguments, and an optional third one shall be added. Behaviour in common databases shall be checked, but I guess ESCAPE belongs to the ISO specification(?)

Sep 17 '14 12:09 doctrinebot

Comment created by @ocramius:

Can you add a few usage examples? What APIs are affected?

Sep 17 '14 13:09 doctrinebot

Comment created by Vicente69:

Sorry, I don't understand what kind of information you're missing. The API is the Doctrine\ORM\Query\Expr class, in the ORM. The need is to improve the like method by addind a third optional parameter, that would allow to push an escape character, which seems to me "standard" SQL. See LIKE specs in vendors documentation, for usage examples:

Oracle 12g: http://docs.oracle.com/database/121/SQLRF/conditions007.htm#SQLRF52141
Mysql 5.6: http://dev.mysql.com/doc/refman/5.6/en/string-comparison-functions.html#operator_like The goal is to be able to generate SQL queries with :

LIKE *pattern_ [ESCAPE _character*]

Actually, the Expr API doesn't support it.

Sep 20 '14 19:09 doctrinebot

Comment created by @ocramius:

[~Vicente69] this is exactly what the kind of feedback I was asking for. As of http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#ebnf, the ESCAPE char is actually supported:

LikeExpression ::= StringExpression ["NOT"] "LIKE" StringPrimary ["ESCAPE" char]

I suppose it just needs to be added to the query builder.

Sep 20 '14 19:09 doctrinebot

Comment created by Vicente69:

Sure, that's it :), using direct DQL for requests, it is possible to write such conditions. However, as you notice it, using the QueryBuilder and the Expr methods, it is not possible to generate such queries. Thanks Marco!

Sep 20 '14 20:09 doctrinebot

Hi, any news? Is Query builder will have escaping string? Do you have information about PR with escaping chars like in http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#ebnf ?

Oct 12 '17 19:10 eugenekurasov

Is Query builder will have escaping string?

Use named or positional parameters.

Oct 12 '17 20:10 Ocramius

Hi, I did not accurately describe the problem. I What about LIKE and chars "%_"? For Example:

$qb = $em->createQueryBuilder('qb');
$string = '%strin_g%'
$qb->select('t')
    ->from('table', 't')
    ->where($qb->expr()->like('t.string', :string)
    ->setParameter('string', $string);

This problem actual for Mysql/Postgresql - so % and _ - is special chars for LIKE. Use LikeExpression you can to escape custom chars. Can I to escape custom chars by QueryBuilder?

Oct 13 '17 07:10 eugenekurasov

ping @Ocramius I can create pull request for escape custom chars for like. To add 3 param for escapeing chars in the like.

$qb->expr()->like('t.string', :string, '%_')

Oct 23 '17 08:10 eugenekurasov

Your idea looks awesome @eugenekurasov How about the status of your PR ?

Feb 21 '19 05:02 bachhuong

Hi, I am am stopped my PR, in future I will be back to this PR, but if you have time you can create PR.

Feb 22 '19 13:02 eugenekurasov

This feature would be particularly useful in PostgreSQL because backslash is the default escape character, what means that you're forced to duplicate backslashes in order to find matches for literal backslashes:

WITH DATA (example) AS
(
    SELECT 'A\pB'
    UNION ALL SELECT 'ApB'
)
SELECT example,
example = 'A\pB' AS equal_is_not_affected,
example LIKE 'A\pB' AS LIKE,
example LIKE 'A\\pB' AS like_escaped_backslash
FROM DATA;

example	equal_is_not_affected	like	like_escaped_backslash
A\pB	true	false	true
ApB	false	true	false

The default escape character is the backslash but a different one can be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.

Source

Feb 28 '24 08:02 kAlvaro