Select substring in Oracle SQL up to a specific character

Question

Let's consider the following example. Say I have a table column called N.Note and it contains the string:

Veg: Lettuce 200 ¶ Fruit: Oranges 200 ¶ Dairy: Milk 300 ¶

This string is not constant and always changes positions. I would like select 'Oranges 200' . How can I edit this line below:

SUBSTR(N.NOTE, INSTR(N.NOTE, 'Fruit:')+6, 18) as Substring

score 0 · Answer 1 · answered Aug 16 '22 at 21:56

As the string after orange ends with an ¶, you can chec for4 the position of that character after fruit comes.

as your last entry also contains and ending character

i added LENGTH additional so that you only need to exchange the category, the rest makes the query then automatically.

Last You can add a TRIMbecause there will be some spaces too much

CREATE TABLE n (note varchar2(200))

INSERT INTO n VALUES ('Veg: Lettuce 200 ¶ Fruit: Oranges 200 ¶ Dairy: Milk 300 ¶')

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 |

db<>fiddle here

score 0 · Answer 2 · answered Aug 17 '22 at 05:13

That's, of course, wrong data model. You should normalize it and skip problems you currently have.

Meanwhile, suppose you'd like to select all fruits from that table. You'd then e.g. - with the following sample data -

SQL> with
  2  n (note) as
  3    (select 'Veg: Lettuce 200 ¶ Fruit: Oranges 200 ¶ Dairy: Milk 300 ¶' from dual union all
  4     select 'Fruit: Apples 400 ¶ Veg: Carrot 200 ¶ Dairy: Tea 100 ¶'    from dual
  5    ),

use such a query: split those strings into rows and fetch only what you're interested in (passed as a parameter. As this is SQL*Plus, I'm using a substitution variable. Depending on a tool you use, that might be a bind variable, :par_what, or something else)

  6  temp as
  7    (select trim(regexp_substr(note, '[^¶]+', 1, column_value)) val
  8     from n cross join
  9       table(cast(multiset(select level from dual
 10                           connect by level <= regexp_count(note, '¶')
 11                          ) as sys.odcinumberlist))
 12    )
 13  select trim(substr(val, instr(val, ':') + 1)) result
 14  from temp
 15  where substr(val, 1, instr(val, ':') - 1) = '&par_what';
Enter value for par_what: Fruit

RESULT
---------------------------------------------------------
Oranges 200
Apples 400

SQL> /
Enter value for par_what: Dairy

RESULT
---------------------------------------------------------
Milk 300
Tea 100

SQL>

Select substring in Oracle SQL up to a specific character

2 Answers2