How to handle this type of Oracle SQL issue

Question

Just have a question for writing SQL.
In ORACLE DB, I have rows of different apples in one "APPLE" TABLE, where the "TAGS" holds all the features of this type of apple. For example:

NAME, TAGS 
-----------
APPLE1, FUJI BOXED MEDIUM CALIFORNIA ...  
APPLE2, ORGANIC GALA PER_POUND LARGE FLORIDA ... 
APPLE3, RED_DELICIOUS MEDIA PACKED ORGANIC ... 
APPLE4, LARGE RED_DELICIOUS Mexico .... 
APPLE5, PACKED FUJI MEXICO LARGE 

Now I want to have a SQL query to find out all rows with any given tag values, For example, "FUJI MEDIUM MEXICO ". How would this SQL be look like ?

This is related to one project I am working on. IN DB, the reason why I have one "TAG" COLUMN to keep all the features, instead of having separate columns, is because we know more and more new tag values will be introduced, so instead of adding more and more columns, we would like to keep them in one column, so that the code does not need to change every time.

Thanks,

Jack

Answer 1

You could redesign the table so it looks like this:

name  | tag
----------
Apple1| FUJI
Apple1| BOXED
...
Apple5| PACKED
Apple5| FUJI

Then to find all items with tags fuji, medium OR mexico you could do this:

SELECT name from tags where tag in ('FUJI','MEDIUM','MEXICO')
GROUP BY name

You could find all items with tags fuji, medium AND mexico with:

SELECT name from tags where tag in ('FUJI','MEDIUM','MEXICO')
GROUP BY name
HAVING count(tag) = 3

(assuming (name,tag) is unique)

This works for any number of tags. Also makes removing tags from items much easier, and allows you to join and sort on the tags too.

Answer 2

I assume that by "FUJI MEDIUM MEXICO" you mean that you want to select apples that are tagged with "FUJI" and "MEDIUM" and "MEXICO", in any order. In that case, the following query would work:

Select name From apple
Where tag like '%FUJI%' 
  And tag like '%MEDIUM%' 
  And tag like '%MEXICO%';

As others have mentioned, if you want a case-insensitive search, then you would want to add appropriate Upper or Lower functions, like so:

Select name From apple
Where Upper(tag) like '%FUJI%' 
  And Upper(tag) like '%MEDIUM%' 
  And Upper(tag) like '%MEXICO%';

For the sake of efficiency, tags should be stored as completely upper case or completely lower case. This would eliminate the need to call the Upper() or Lower() function on the tag value of each row, which could save a lot of time if the data set were very large.

Answer 3

Bad table design aside, this can be accomplished using a like evaluation.

select
   apple
   tags
from
   table
where
   lower(tags) like '%tag_here%'

I've used the lower() function here to make dealing with string casing easier. When you replace tag_here do so with all lowercase characters.

---

That being said, you really should improve your database design. This is very inefficient from both a storage and a performance standpoint. A better design would have two different tables. One would store the apples and a second table would store the tags with a foreign key back to the apples table.

Answer 4

Better design will be your friend here.

Three tables:

CREATE TABLE APPLE_TYPE
  (APPLE_TYPE    VARCHAR2(100));

CREATE TABLE APPLE_ATTRIBUTES
  (ATTRIBUTE_TYPE  VARCHAR2(100));

CREATE TABLE APPLES
  (APPLE_ID        NUMBER,
   APPLE_TYPE      VARCHAR2(100)
     CONSTRAINT APPLES_FK1
       REFERENCES APPLE_TYPE(APPLE_TYPE)
         ON DELETE CASCADE,
   ATTRIBUTE_TYPE  VARCHAR2(100)
     CONSTRAINT APPLES_FK2
       REFERENCES APPLE_ATTRIBUTES(ATTRIBUTE_TYPE)
         ON DELETE NO ACTION);

Best of luck.

Answer 5

I would create some of these tags as columns and create a second table for the "miscellaneous" tags.

Table: Apples
Apple_ID  PK
Name
Where_Grown
Size

Table:  Apple_Tags
Tag_ID PK
Apple_ID FK 
Tag

Index:  Apple_Tags.Tag, Apple_ID

Data for Apple 1 is:
Apples Table
ID:  1
Name: Fuji
Where_Grown:  California
Size: Medium

Tags Table
Tag_ID: 1
Apple_ID: 1
Tag:  Boxed

To find tags:

select * from apples a inner join apple_tags t on a.apple_id = t.apple_id

Notice I'm not storing multiple tags in one column. That breaks the first rule of normalization that columns are atomic. I'm storing them as rows in a separate table. I'm also recognizing that apple name, size, and the place where it's grown are attributes common to all apples.