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.
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
CONCAT() is probably the most technically correct.
For convenience, however, ORACLE does have an equivalent operator to +...
SELECT *
FROM MATERIALS
WHERE longname LIKE (shortname || '%')
|| is the technically correct and + is non-standard.It may be worth mentioning that neither of the above worked for me in an Oracle 19c database today.
SITUATION
I had created a function that I could query like a table.
(A PIPELINED function returning a custom data type).
In the loop to return a (modified) row of data, I wanted to take a parameter from the function call and use a LIKE to identify the right data.
I started with hard coded values, and everything worked fine. When I moved to dynamically assigning the value for the like, in the ways mentioned in the answer by @MatBailie and @ypercube™. Neither worked.
FIX
What DID work, however was to do the following:
localVariable := parameterPassedToFunction||'%';LIKE''||localVariableHope that helps someone in the future.
