sqloracle-database

Search clob ignore case


I am trying to find a phrase within a CLOB and I got it to work with the setup and query below.

My question is whether there a way I can do this without converting the case. For example, the CLOB is inserted with all lower case or mixed case. I'm looking for a CASE independent query if that makes sense. Note I'm not married to the LIKE command if there is something I can use that will ignore case.

Below is my test CASE.

create table recipes(
  seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
description VARCHAR2(50),
ingredients CLOB,
directions CLOB
);

INSERT into recipes (
   description, 
   ingredients,
   directions
) VALUES
('Italian Stuffed Peppers',
'
6 large bell peppers cut in half and seeds removed
1/4 cup olive oil divided
1 1/2 pounds ground chuck
2 cups cooked white rice
8 cloves garlic minced
1 cup low sodium beef stock
1 cup Pecorino Romano grated
1/4 cup parsley minced
1/4 cup basil chopped
1/4 teaspoon crushed hot red pepper flakes
2 teaspoons dried oregano
4 cups marinara sauce divided, see notes below
3/4 cup water
1 cup mozzarella grated
2 1/2 teaspoons kosher salt divided, plus more to taste
',
'XXX'
);

SELECT 
      seq_num, 
      ingredients 
FROM RECIPES
WHERE INGREDIENTS LIKE LOWER('%OLIVE%OIL%');


Solution

  • You can create an Oracle Text index on the column:

    CREATE INDEX recipes__ingredients__textidx ON recipes(ingredients)
      INDEXTYPE IS CTXSYS.CONTEXT;
    

    Then:

    SELECT seq_num, 
           ingredients 
    FROM   RECIPES
    WHERE  CONTAINS(ingredients, 'OLIVE OIL', 1) > 0;
    

    Outputs:

    SEQ_NUM INGREDIENTS
    1
    6 large bell peppers cut in half and seeds removed
    1/4 cup olive oil divided
    1 1/2 pounds ground chuck
    2 cups cooked white rice
    8 cloves garlic minced
    1 cup low sodium beef stock
    1 cup Pecorino Romano grated
    1/4 cup parsley minced
    1/4 cup basil chopped
    1/4 teaspoon crushed hot red pepper flakes
    2 teaspoons dried oregano
    4 cups marinara sauce divided, see notes below
    3/4 cup water
    1 cup mozzarella grated
    2 1/2 teaspoons kosher salt divided, plus more to taste

    fiddle