Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 157 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 389 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
SQL Logic for using LIKE condition based First keyword of a column value with another table

We have a requirement where there are two tables with no keys between them (Master Table and Reference Table)
The Master table has Name and Location columns, Reference table has Category and Type columns.
Master table Location column should check values in Reference table Type column and then give the relevant Category from the Reference table. This issue has been solved in other post
SQL Logic for using LIKE condition based on column values of another table
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.
Please do suggest if this is achievable or not? and also please do suggest any recommendations.
Thank you so much for your help.
-Rama
CREATE TABLE MASTER_TABLE(NAME VARCHAR2(100), LOCATION VARCHAR2(100));
INSERT INTO MASTER_TABLE VALUES ('John','005 3rd Floor Hallway');
INSERT INTO MASTER_TABLE VALUES ('David','Hallway utility');
INSERT INTO MASTER_TABLE VALUES ('Ashok','playroom');
INSERT INTO MASTER_TABLE VALUES ('Kyle','playroom bathroom');
INSERT INTO MASTER_TABLE VALUES ('Srikanth','Outdoor activity');
INSERT INTO MASTER_TABLE VALUES ('Pavan','Utility Outdoor');
INSERT INTO MASTER_TABLE VALUES ('Mike','Other');
CREATE TABLE REFERENCE_TABLE (CATEGORY VARCHAR2(100), TYPE VARCHAR2(100));
INSERT INTO REFERENCE_TABLE VALUES ('Hallway, Stairs, Lobby','Hallway');
INSERT INTO REFERENCE_TABLE VALUES ('Basement, mechanical, storage','utility');
INSERT INTO REFERENCE_TABLE VALUES ('Community','playroom');
INSERT INTO REFERENCE_TABLE VALUES ('office','bathroom');
INSERT INTO REFERENCE_TABLE VALUES ('Other','other');
select * from REFERENCE_TABLE;
select * from MASTER_TABLE;
Answers
-
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.
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