Select substring in Oracle SQL up to a specific character-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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>