Search code examples
sqlsql-like

How does SQL's LIKE work in case of Path Enumeration?

I am reading the book SQL Antipatterns where a SQL query is used like this:

SELECT *
FROM Comments AS c
WHERE '1/4/6/7/' LIKE c.path || '%';

to find ancestors of comment #7 from this table:

enter image description here

I am not much familiar with the regex employed for LIKE and would appreciate understanding how it does its work. Specifically, does it matter that the literal '1/4/6/7' is located on the left hand of the LIKE keyword? And how does the entire WHERE predicate work (i.e. || '%')?

Solution

  • First of all, in case it is not clear, the || is the string concatenation operator. So, if the value of c.path is '1/', then c.path || '%' yields '1/%'.

    So, obviously, you cannot do WHERE field LIKE 'constant%' because in this particular (weird) kind of query it is the constant that may be longer than the field, and not the other way around.

    Usually, what we do with LIKE is WHERE field LIKE 'constant%' to check whether the value of the field starts with the constant. Here the author of the query wants to see whether the constant starts with the value of the field, which is a bizarre thing to do.