SQL startswith (using `LIKE`) on an expression

Question

What's an appropriate way to do startswith(expression) in SQL?

I can do it with LIKE ((expression) || '%'), but it doesn't look very nice to me.

Full query is in form:

SELECT …, (SELECT COUNT(*)             FROM post AS child             WHERE child.path LIKE (post.path || '%')            AND child.depth >= post.depth) FROM post WHERE … 

I suppose it is preferable to use LIKE because of DB indexing for this case.

Answer 1

Just use LIKE 'input%'. I.E:

WHERE child.path LIKE post.path + '%' 

(I assume this is for SQL Server, though this syntax probably works elsewhere)

Answer 2

In standard SQL, you can also say:

where position(post.path in child.path) = 1 

I don't know if your RDBMS supports that. PostgreSQL does.