Show LIKE condition matches in a column

Question

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?

score 5 · Accepted Answer · answered Jul 12 '16 at 13:06

5

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 );

answered Jul 12 '16 at 13:06

MT0

143,790
11
59
117

I want to point out that this will return multiple rows for each `fname`/`lname` combination if there are multiple matches. – Gordon Linoff Jul 12 '16 at 14:23
1

@GordonLinoff The principle was to show that you can use a collection to store all the match conditions and compare it to the data table. There are multiple ways to aggregate the match conditions into a single column that that should not be an issue - `LISTAGG()` being the immediately obvious. You can even [pass the collection as a bind variable](http://stackoverflow.com/a/34699771/1509264) then you do not need to statically define the match conditions in the query. – MT0 Jul 12 '16 at 14:37

score 0 · Answer 2 · answered Jul 12 '16 at 13:04

You can do this in SQL. Here is one method:

select n.*
from (select n.*,
             ((case when gcode LIKE %5536%  then '5536;' end) ||
              (case when gcode LIKE %3663%  then '3663;' end) ||
              . . .
             ) as matches
      from names
     ) n
where matches is not null;

Show LIKE condition matches in a column

2 Answers2