marius_neo marius_neo - 1 month ago 6
SQL Question

Dynamic 'LIKE' Statement in SQL (Oracle)

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

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

Comments