daneshjai daneshjai - 4 months ago 16
SQL Question

Show LIKE condition matches in a column

I have a similar query:

SELECT fname, lname
FROM names
WHERE gcode LIKE %5536% OR fname LIKE %3663%


There are 50 conditions like this, and the terms are a bit longer, but that is the general idea. We can't make temporary tables so this is the route we're going.

I make trying to return the like condition that the output matched on in its own column (i.e., have
%5536%
be returned in its own column)

I can highlight it programmatically, but is there a way to do this in SQL?

MT0 MT0
Answer

You can store the match conditions in a collection:

SELECT n.fname,
       n.lname,
       t.COLUMN_VALUE AS match
FROM   name n
       INNER JOIN
       TABLE( SYS.ODCIVARCHAR2LIST( '%5536%', '%3663%' ) ) t
       ON ( n.gcode LIKE t.COLUMN_VALUE );