By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE n (note varchar2(200))
INSERT INTO n VALUES ('Veg: Lettuce 200 ¶ Fruit: Oranges 200 ¶ Dairy: Milk 300 ¶')
1 rows affected
SELECT SUBSTR(N.NOTE
, INSTR(N.NOTE, 'Fruit:')+ LENGTH('Fruit:'),INSTR(N.NOTE,'¶',INSTR(N.NOTE, 'Fruit:')+ LENGTH('Fruit:'), 1) - 1 - INSTR(N.NOTE, 'Fruit:') - LENGTH('Fruit:') ) as Substring FROM n
| SUBSTRING |
|---|
| Oranges 200 |
SELECT INSTR(N.NOTE,'¶',INSTR(N.NOTE, 'Fruit:')+ LENGTH('Fruit:'), 1) - 1 - INSTR(N.NOTE, 'Fruit:') - LENGTH('Fruit:') FROM n
| INSTR(N.NOTE,'¶',INSTR(N.NOTE,'FRUIT:')+LENGTH('FRUIT:'),1)-1-INSTR(N.NOTE,'FRUIT:')-LENGTH('FRUIT:') |
|---|
| 12 |
SELECT SUBSTR(N.NOTE
, INSTR(N.NOTE, 'Dairy:')+ LENGTH('Dairy:'),INSTR(N.NOTE,'¶',INSTR(N.NOTE, 'Dairy:')+ LENGTH('Dairy:'), 1) - 1 - INSTR(N.NOTE, 'Dairy:') - LENGTH('Dairy:') ) as Substring FROM n
| SUBSTRING |
|---|
| Milk 300 |