Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

MATCH EXACT WORDS

TobyJan 6 2021

Hi, I have some trouble matching some words. I will explain with data:
I have some EVALUATION WORDS in a table, classified as CLASS as follows:
CLASS EVALUATION WORDS
100 TERRACOTA, QUARRY, STONE
101 SLATE, LEDGER, MARBLE, SANDSTONE, LIMESTONE
.
.
.

I need to retrieve the information that match the exact word, for example, if I want to retrieve STONE, i shouldn't get SANDSTONE and LIMESTONE, I must get class 100.
My query is something like this:
SELECT DISTINCT ig.class INTO out_class FROM item_groups ig WHERE ig.evaluation_words LIKE '% '||upper(trim(evaluation_word))||'%';
If I run this query, I will get class 100 and 101. I just need class 100 because there is the word STONE.
In my table I have a lot of classes and evaluation words per class, and the above query is not working for specific cases where the word is inside another word, as STONE inside LIMESTONE.

Thanks in advance
Khristine

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 2 2024
Added on Jan 6 2021
9 comments
778 views