SQL Developer

Based on some help in my previous post, I have used the below SQL. But not able to figure out logic when I have multiple keywords and then picking the first keyword.
https://community.oracle.com/tech/developers/discussion/4484475/sql-logic-for-using-like-condition-based-on-column-values-of-another-table

SELECT   *
FROM     master_table    m
JOIN     reference_table r  ON LOWER (m.location) LIKE '%' || LOWER (r.type) || '%'
ORDER BY m.name, r.type

-Rama

Hi, @rama0830
But here I have a complex scenario, if there are multiple keywords under the Location column then we should pick only the first keyword and should map it to the Reference table Type column.
For example (from the screenshot below):-
For David, the value in the Location column is **Hallway utility (**two keywords wrt Reference table). So it should first check the first keyword which is Hallway and then map it to the Reference table to give output as Hallway, Stairs, Lobby.
...
INSERT INTO MASTER_TABLE VALUES ('John','005 3rd Floor Hallway');
INSERT INTO MASTER_TABLE VALUES ('David','Hallway utility');
Okay, for 'David', the first word in the location column is 'Hallway', so you want to ignore 'utility' when matching 'David'.
For 'John', the first word in the location column is '005'. Does that mean you want to ignore '3rd', 'Floor' and 'Hallway'?

Hi, Rama0830
Do you mean when two or more rows from reference_table match the same row from master_table, then you only want the one that matched earlier in location? For example, for 'John', location = 'Hallway utility'. This matches two rows in reference_table
type = 'Hallway' (found at position 1 in 'Hallway utility', and
type = 'utility' (found at position 9 of 'Hallway utility'
So, in this case, you only want the result set to include the match from position 1. Is that right?

Here's one way to do that:

WITH  all_matches  AS
(
  SELECT  m.*, r.*
  ,	  ROW_NUMBER () OVER ( PARTITION BY m.name
			       ORDER BY	     INSTR ( LOWER (m.location)
  	  		   	  	 	   , LOWER (r.type)
					 	   )
  	  	    	      ) AS rn
  FROM  master_table   m
  JOIN  reference_table r ON INSTR ( LOWER (m.location)
  	  		   	   , LOWER (r.type)
				   ) > 0
)
SELECT   name, location, category, type
FROM	 all_matches
WHERE	 rn = 1
;

Notice this uses INSTR instead of LIKE. Whenever you're tempted to use LIKE, also consider INSTR; sometimes it can do the same job, and do it more efficiently.

select *
from master_table mt
outer apply (
 select type, category
 from reference_table rt
 where    instr(' '||translate(mt.location,',',' ')||' ' collate binary_ci, ' '||rt.type||' ') > 0
 order by instr(' '||translate(mt.location,',',' ')||' ' collate binary_ci, ' '||rt.type||' ')
 fetch first 1 row only
);

NAME       LOCATION                TYPE       CATEGORY                       
John       005 3rd Floor Hallway   Hallway    Hallway, Stairs, Lobby          
David      Hallway utility         Hallway    Hallway, Stairs, Lobby          
Ashok      playroom                playroom   Community                       
Kyle       playroom bathroom       playroom   Community                       
Srikanth   Outdoor activity        outdoor    Exterior                        
Pavan      Utility Outdoor         utility    Basement, mechanical, storage   
Mike       Other                   other      Other