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%');
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 |