Need help writing a query to pull a substring-CodePudding

I'm attempting to pull data from a string -

'./Iowa/Hospital/Main Campus/Maternal Child/PEDS/RN'.

I"m using SQL Developer on an Oracle DB. I need to pull 'Main Campus' from the string and I'm having trouble with the query. This is what I've tried, but I get 'Main Campus/Maternal C':

select
    substr(
        primaryorgpathnm, instr(primaryorgpathnm,'/',1,3)   1, 
        length(primaryorgpathnm) - instr(primaryorgpathnm,'/',-1,3))
from
    vp_primaryorg

Any help greatly appreciated! Thanks!

CodePudding user response：

This will give you the desired result:

select
       regexp_substr(primaryorgpathnm, '[^/] ', 1, 4) as primaryorgpathnm
from 
       vp_primaryorg

Ref: https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm

Result:

primaryorgpathnm
================
Main Campus

CodePudding user response：

By counting the slashes from the beginning everywhere, we can write

select
    substr(
        primaryorgpathnm,
        instr(primaryorgpathnm,'/',1,3)   1, 
        instr(primaryorgpathnm,'/',1,4) - instr(primaryorgpathnm,'/',1,3) - 1
    ) as result
from
    vp_primaryorg

The third argument to substr calculates the length of the string between the 3rd and 4th /.

See: http://sqlfiddle.com/#!4/7f040d/4/0

CodePudding user response：

If you want to extract based on the length of the element of the string then you need to count the number of characters between the two enclosing slashes - not base it on the overall length of the string:

select
    substr(
        primaryorgpathnm, instr(primaryorgpathnm,'/',1,3)   1, 
        instr(primaryorgpathnm,'/',1,4) - instr(primaryorgpathnm,'/',1,3) - 1)
from
    vp_primaryorg

Or you can use a regular expression:

select
    regexp_substr(
        primaryorgpathnm, '(.*?)(/|$)', 1, 4, null, 1
    )
from
    vp_primaryorg

db<>fiddle