Split one row from dual into several different columns-CodePudding

How to use regular expressions in such a case?

select 'John Doe   jr.' from dual;

As a result, I would like to see it like this:

col 1	col2	col3
John	Doe	jr.

There is a line that contains spaces, you need to remove all spaces from the line and split it into columns.

I use Oracle 19c

CodePudding user response：

With sample data you posted, this is one option - return the 1st, 2nd and 3rd word (followed by a dot):

SQL> with test (col) as
  2    (select 'John Doe   jr.' from dual)
  3  select regexp_substr(col, '\w ', 1, 1) col1,
  4         regexp_substr(col, '\w ', 1, 2) col2,
  5         regexp_substr(col, '\w .', 1, 3) col3
  6  from test;

COL1 COL COL
---- --- ---
John Doe jr.

SQL>