Dynamic 'LIKE' Statement in SQL (Oracle)

Question

I am trying to select from a table with the following structure :

MATERIALS 
id
shortname 
longname

all the lines where the long name is like the short name.

I've tried the solution presented here : Dynamic Like Statement in SQL , but it doesn't work for me.

SELECT * from MATERIALS where longname like (shortname + '%');

doesn't work in Oracle.

Answer 1

You can use the CONCAT() function:

SELECT * 
FROM MATERIALS 
WHERE longname LIKE CONCAT(shortname, '%')

or even better, the standard || (double pipe) operator:

SELECT * 
FROM MATERIALS 
WHERE longname LIKE (shortname || '%')

---

Oracle's CONCAT() function does not take more than 2 arguments so one would use the cumbersome CONCAT(CONCAT(a, b), c) while with the operator it's the simple: a || b || c