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
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
