4

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.

Community
  • 1
  • 1
marius_neo
  • 1,535
  • 1
  • 13
  • 28
  • 2
    I found the answer in the meantime : select * from materials where longname like CONCAT(upper(shortname), '%'); – marius_neo Nov 30 '11 at 14:42

2 Answers2

12

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

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
2

CONCAT() is probably the most technically correct.

For convenience, however, ORACLE does have an equivalent operator to +...

SELECT * 
FROM MATERIALS 
WHERE longname LIKE (shortname || '%')
MatBailie
  • 83,401
  • 18
  • 103
  • 137