CODEWITHSUNDEEP
oracle-database
Jack Zollo
Jack Zollo

Reputation: 13

Oracle regexp_substr to get first portion of a string

I am stuck here. I am using oracle and I want to get the first part of a string before the first appearance of '|'. This is my query but it returns the last part i.e 25.0. I want it to return first part i.e 53. How do I achieve that?

select regexp_substr('53|100382951130|25.0', '[^|]+$',  1,1) as part1 from dual

Upvotes: 0

Views: 308

Answers (1)

Aleksej
Aleksej

Reputation: 22969

Assuming you always have at least one occurrence of '|', you can use the following, with no regexp:

with test(string) as ( select '53|100382951130|25.0' from dual)    
select substr(string, 1, instr(string, '|')-1)
from test

You could even use regexp to achieve the same thing, or even handle the case in which you have no '|':

with test(string) as (
    select '53|100382951130|25.0' from dual union all
    select '1234567' from dual)    
select string,
       substr(string, 1, instr(string, '|')-1),
       regexp_substr(string, '[^|]*')
from test

You can even handle the case with no occurrence of '|' without regexp:

with test(string) as (
    select '53|100382951130|25.0' from dual union all
    select '1234567' from dual)   
select string,
       substr(string, 1, instr(string, '|')-1),
       regexp_substr(string, '[^|]*'),
       substr(string, 1,
                         case
                           when instr(string, '|') = 0
                             then length(string)
                           else 
                             instr(string, '|')-1
                         end
             )
from test

Upvotes: 2

Related Questions