Split String by delimiter position using oracle SQL
Solution 1
You want to use regexp_substr()
for this. This should work for your example:
select regexp_substr(val, '[^/]+/[^/]+', 1, 1) as part1,
regexp_substr(val, '[^/]+$', 1, 1) as part2
from (select 'F/P/O' as val from dual) t
Here, by the way, is the SQL Fiddle.
Oops. I missed the part of the question where it says the last delimiter. For that, we can use regex_replace()
for the first part:
select regexp_replace(val, '/[^/]+$', '', 1, 1) as part1,
regexp_substr(val, '[^/]+$', 1, 1) as part2
from (select 'F/P/O' as val from dual) t
And here is this corresponding SQL Fiddle.
Solution 2
Therefore, I would like to separate the string by the furthest delimiter.
I know this is an old question, but this is a simple requirement for which SUBSTR and INSTR would suffice. REGEXP are still slower and CPU intensive operations than the old subtsr and instr functions.
SQL> WITH DATA AS
2 ( SELECT 'F/P/O' str FROM dual
3 )
4 SELECT SUBSTR(str, 1, Instr(str, '/', -1, 1) -1) part1,
5 SUBSTR(str, Instr(str, '/', -1, 1) +1) part2
6 FROM DATA
7 /
PART1 PART2
----- -----
F/P O
As you said you want the furthest delimiter, it would mean the first delimiter from the reverse.
You approach was fine, but you were missing the start_position in INSTR. If the start_position is negative, the INSTR
function counts back start_position number of characters from the end of string and then searches towards the beginning of string.
Related videos on Youtube
Avinesh Kumar
I am an Analyst Programmer at The University of the South Pacific and I manage student data at the university. I am currently doing my Masters and i am specialised in Data Security, Information Systems, Computing Science and Software Engineering.
Updated on July 09, 2022Comments
-
Avinesh Kumar almost 2 years
I have a string and I would like to split that string by delimiter at a certain position.
For example, my String is
F/P/O
and the result I am looking for is:Therefore, I would like to separate the string by the furthest delimiter.
Note: some of my strings areF/O
also for which my SQL below works fine and returns desired result.The SQL I wrote is as follows:
SELECT Substr('F/P/O', 1, Instr('F/P/O', '/') - 1) part1, Substr('F/P/O', Instr('F/P/O', '/') + 1) part2 FROM dual
and the result is:
Why is this happening and how can I fix it?
-
glenn garson about 6 yearssimple is better: substr and instr is simpler than 'regexp_substr'
-
glenn garson about 6 yearsAs soon as I made the comment about simpler (see below) I ran into a situation that was not simple. Thank you both Gordon Linoff, and Lalit Kumar B.