Split String by delimiter position using oracle SQL

by

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, "https://stackoverflow.com/", -1, 1) -1) part1,
  5         SUBSTR(str, Instr(str, "https://stackoverflow.com/", -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.

More Related Contents:

  1. Split comma separated values to columns in Oracle
  2. Split comma separated column data into additional columns
  3. How to Select a substring in Oracle SQL up to a specific character?
  4. Split comma separated values of a column in row, through Oracle SQL query
  5. oracle — Split multiple comma separated values in oracle table to multiple rows
  6. Oracle- Split string comma delimited (string contains spaces and consecutive commas)
  7. ORA-01843 Not a valid month
  8. Merging two tables in SQL [closed]
  9. Inner join vs Where
  10. How do I do top 1 in Oracle? [duplicate]
  11. How to declare variable and use it in the same Oracle SQL script?
  12. How do I split a delimited string so I can access individual items?
  13. Are a CASE statement and a DECODE equivalent?
  14. SQL recursive query on self referencing table (Oracle)
  15. Why use a JOIN clause versus a WHERE condition?
  16. format interval with to_char
  17. Oracle SQL – Identify sequential value ranges
  18. Get month name from date in Oracle
  19. OVER clause in Oracle
  20. SQL Error: ORA-00942 table or view does not exist
  21. PARTITION BY with and without KEEP in Oracle
  22. Different CURRENT_TIMESTAMP and SYSDATE in oracle
  23. How to return multiple rows from the stored procedure? (Oracle PL/SQL)
  24. How to extract week number in sql
  25. How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?
  26. Using LIKE in an Oracle IN clause
  27. In Oracle, is it possible to INSERT or UPDATE a record through a view?
  28. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  29. Exporting a CLOB to a text file using Oracle SQL Developer
  30. Oracle ‘Partition By’ and ‘Row_Number’ keyword

Leave a Comment