Split String by delimiter position using oracle SQL

Question

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

User · Answer

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

User · Answer

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

[sql] Split String by delimiter position using oracle SQL

Examples related to sql

Examples related to oracle

Examples related to split

Examples related to substring

Examples related to delimiter