regex REGEXP_SUBSTR split into the columns

Question

Could you please help me to write a regex to split the string into the columns?

Input:

select REGEXP_SUBSTR ('/D 1/D 18/15/1000', xxxx)  from dual ;

or

select REGEXP_SUBSTR ('/D 1/D 18/15', xxxx)   from dual ;

Output:

col1>D 1<  col2 >D 18< col3 >15<  col4>1000<

or

col1>D 1< col2>D 18< col3>15<

The result should bring columns. So it means I want to split into the columns.

Answer 1

To get the output you are expecting, you don't need to use regex. You can use a simple REPLACE to replace the / with a space, then trim any spaces from the ends of the string.

SQL> SELECT TRIM (REPLACE ('/D 1/D 18/15/1000', '/', ' ')) AS sample_data FROM DUAL;


        SAMPLE_DATA
___________________
D 1 D 18 15 1000


SQL> SELECT TRIM (REPLACE ('/D 1/D 18/15', '/', ' ')) AS sample_data FROM DUAL;


   SAMPLE_DATA
______________
D 1 D 18 15

Update

Based on information in comments, the values should be split into separate columns. The regex below can be used to get the different values by splitting using the / as a delimiter.

SQL> WITH sample_data AS (SELECT '/D 1/D 18/15/1000' AS sample_col FROM DUAL)
  2  SELECT regexp_substr(sample_col, '[^/]+',1,1) as col1,
  3         regexp_substr(sample_col, '[^/]+',1,2) as col2,
  4         regexp_substr(sample_col, '[^/]+',1,3) as col3,
  5         regexp_substr(sample_col, '[^/]+',1,4) as col4,
  6         regexp_substr(sample_col, '[^/]+',1,5) as col5
  7    FROM sample_data;


   COL1    COL2    COL3    COL4    COL5
_______ _______ _______ _______ _______
D 1     D 18    15      1000


SQL> WITH sample_data AS (SELECT '/D 1/D 18/15' AS sample_col FROM DUAL)
  2  SELECT regexp_substr(sample_col, '[^/]+',1,1) as col1,
  3         regexp_substr(sample_col, '[^/]+',1,2) as col2,
  4         regexp_substr(sample_col, '[^/]+',1,3) as col3,
  5         regexp_substr(sample_col, '[^/]+',1,4) as col4,
  6         regexp_substr(sample_col, '[^/]+',1,5) as col5
  7    FROM sample_data;


   COL1    COL2    COL3    COL4    COL5
_______ _______ _______ _______ _______
D 1     D 18    15