Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 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
- 158 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
- 400 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
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
SQL Logic for using LIKE condition based on column values of another table

We have a requirement where there are two tables with no keys between them (Master Table and Reference Table)
I have attached the script and also the excel spreadsheet with the expected results.
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.
For example (from the attachment):-
For John, in the location column, there is Hallway, so it should check-in from the Type column in the Reference table and give output as Hallway, Stairs, Lobby.
I tried different methods with LIKE condition but failed.
Please do suggest if this is achievable or not? and also please do suggest any recommendations.
Thank you so much for your help.
-Rama
Best Answer
-
Hi, @Rama0830
Thanks for posting the sample data and results. Wouldn't it be better to test with some cases where a master row matched more than one reference row, or didn't match any, like this?
INSERT INTO MASTER_TABLE (name, location) VALUES ('Rama0830', 'Displayroom Hallway'); INSERT INTO MASTER_TABLE (name, location) VALUES ('Fubar', 'Parking Lot');
The Solution Solomon posted about 45 hours ago almost does what you now requested. It assumes you're looking for an exact match (e.g. 'Recptionist' is different from 'Reception') and that the matches are case-sensitive. Since you want 'Receptionist' to match 'reception', you can modify Solomon's solution like this:
SELECT * FROM master_table m JOIN reference_table r ON LOWER (m.location) LIKE '%' || LOWER (r.type) || '%' ORDER BY m.name, r.type -- or whatever you want /
In this case, you could also use INSTR instead of LIKE.
Answers
-
Hi, @Rama0830
I have attached the script and also the excel spreadsheet with the expected results.
Post everything right in this space. Not everyone who wants to help you can or will open attachments. Always give your full Oracle database version (e.g. 12.2.0.1.0).
I tried different methods with LIKE condition but failed.
If it's worth mentioning this, then it's worth posting what you tried, at least what you think was your best attempt. It can help explain what you need to do.
-
Hi, you can use XMLAGG function. You can create a function that returns aggregated string or you can use as as a sub query.
CREATE OR REPLACE FUNCTION SAMPLE_XML_AGG (P_SEARCH_KEY IN VARCHAR2) RETURN VARCHAR2 IS
P_DUMMY VARCHAR2(4000);
BEGIN
SELECT SUBSTR ( REPLACE ( REPLACE (XMLAGG (XMLELEMENT ("x", TMP.DETAIL_TABLE_COLUMN)), '</x>'), '<x>', ','), 2, 4000) INTO P_DUMMY
FROM ( SELECT L.DETAIL_TABLE_COLUMN FROM DETAIL_TABLE L
WHERE (L.REF_COLUMN = P_SEARCH_KEY)
GROUP BY L.REF_COLUMN
) TMP;
RETURN P_DUMMY;
END;
-
select * from master_table m, reference_table r where ' ' || m.location || ' ' like '% ' || r.type || ' %' / NAME LOCATION CATEGORY TYPE ---------- ------------------------------ ------------------------------ ---------- Pavan private utility Basement, mechanical, storage utility Mike utility room Basement, mechanical, storage utility John 005 3rd Floor Hallway Hallway, Stairs, Lobby Hallway David 11 floor Hallway Hallway, Stairs, Lobby Hallway SQL>
SY.
-
Question is if words in location are separated by spaces and aren't followed by punctuation (or other non-space characters). Then such characters must be replaced by spaces first:
SQL> insert 2 into master_table 3 values('Jim','3rd Floor Hallway, Building 9') 4 / 1 row created. SQL> select * 2 from master_table m, 3 reference_table r 4 where ' ' || m.location || ' ' like '% ' || r.type || ' %' 5 / NAME LOCATION CATEGORY TYPE ---------- ---------------------------------------- ------------------------------ ---------- Pavan private utility Basement, mechanical, storage utility Mike utility room Basement, mechanical, storage utility John 005 3rd Floor Hallway Hallway, Stairs, Lobby Hallway David 11 floor Hallway Hallway, Stairs, Lobby Hallway SQL> select * 2 from master_table m, 3 reference_table r 4 where ' ' || regexp_replace(m.location,'\W',' ') || ' ' like '% ' || r.type || ' %' 5 / NAME LOCATION CATEGORY TYPE ---------- ---------------------------------------- ------------------------------ ---------- Pavan private utility Basement, mechanical, storage utility Mike utility room Basement, mechanical, storage utility John 005 3rd Floor Hallway Hallway, Stairs, Lobby Hallway David 11 floor Hallway Hallway, Stairs, Lobby Hallway Jim 3rd Floor Hallway, Building 9 Hallway, Stairs, Lobby Hallway SQL>
SY.
-
Sure next time, I will make sure to put everything in the space.
-
Thank you all for your responses. It did help..
I should have been more careful with my question if the result can be achieved from just using CASE statement?
Ex:-
select m.location, case when ' ' || m.location || ' ' like '% ' ||(select distinct r.type from reference_table r)|| ' %' then r.category else 'NA' end Category from master_table m
The above query won't work. Can you please help me with this step?
Thank you,
Rama
-
Why wait until next time? What's wrong with this time?
Obviously your question is not answered yet. I might be interested in helping you, but I am not going to open attachments from an unknown source. If others aren't concerned about the risks, that's fine, but I am sure you would have had more interest in your question (from more forum participants) if you had the data posted here, rather than through attachments.
-
Hi, @Rama0830
I should have been more careful with my question if the result can be achieved from just using CASE statement?
Ex:-
select m.location, case when ' ' || m.location || ' ' like '% ' ||(select distinct r.type from reference_table r)|| ' %' then r.category else 'NA' end Category from master_table m
The above query won't work.
That's right; a scalar sub-query, such as
(select distinct r.type from reference_table r)
, will raise an error if it produces more than one row.By the way, what you posted is a CASE expression, not a CASE statement. A CASE statement is something different.
Can you please help me with this step?
Can you post the sample data and desired results right in this space, and not in an attachment?
-
Hi,
Here you go..
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','11 floor Hallway');
INSERT INTO MASTER_TABLE
VALUES ('Ashok','Receptionist Desk');
INSERT INTO MASTER_TABLE
VALUES ('Kyle','BATHROOM 1 (A/B)');
INSERT INTO MASTER_TABLE
VALUES ('Srikanth','Health Club Child Playroom');
INSERT INTO MASTER_TABLE
VALUES ('Pavan','private utility');
INSERT INTO MASTER_TABLE
VALUES ('Mike','utility room');
CREATE TABLE REFERENCE_TABLE
(CATEGORY VARCHAR2(100),
TYPE VARCHAR2(100));
INSERT INTO REFERENCE_TABLE
VALUES ('Basement, mechanical, storage','utility');
INSERT INTO REFERENCE_TABLE
VALUES ('Community','playroom');
INSERT INTO REFERENCE_TABLE
VALUES ('Community','bathroom');
INSERT INTO REFERENCE_TABLE
VALUES ('Hallway, Stairs, Lobby','Hallway');
INSERT INTO REFERENCE_TABLE
VALUES ('Hallway, Stairs, Lobby','reception');
select * from REFERENCE_TABLE;
select * from MASTER_TABLE;
Thank you for your time and help.
-
I have provided the script in the above comment.
Below is the expected results.
Thank you,