'Is there a SUBSTR alternative in Oracle SQL

I'm trying to extract the the different sub-strings within one string. The I want different strings for every string divided by the dash (-) symbol.

I have tried using the SUBSTR position function. It does not work since sometimes there are 4 chars in the second sub string, therefore, the 3rd sub string is not correct.

SELECT SUBSTR(STR, INSTR (STR, '-', -1)+ 1)

STR = F-123-A123-B12 or F-1234-A123-B12
  • I am trying to get a query that will give me F.
  • I need another query that will give me 123 or 1234 if there are 4 chars
  • I need another query to get me A123
  • I need another query to get B12

I was thinking there would be a regex function that I could use. I could not find one.

sqloracle


Solution 1:[1]

For example:

SQL> with test (col) as
  2    (select 'F-123-A123-B12' from dual)
  3  select regexp_substr(col, '\w+', 1, level) result
  4  from test
  5  connect by level <= regexp_count(col, '-') + 1;

RESULT
--------------
F
123
A123
B12

SQL>

Solution 2:[2]

If your string could have a NULL element, use this format to handle it (Note list element 2 is NULL), else you risk the following elements being returned in the wrong positions:

with test (col) as 
      (select 'F--A123-B12' from dual)
    select regexp_substr(col, '(.*?)(-|$)', 1, level, null, 1) result
    from test
    connect by level <= regexp_count(col, '-') + 1;

RESULT     
-----------
F          

A123       
B12        

4 rows selected.

Solution 3:[3]

I am working with newest Node 17.3.1 where "substr" function is obsolete and I replace it with "substring" instead and it is the nearest and simplest alternative.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Littlefoot
Solution 2 Gary_W
Solution 3 TLCW

Related Questions