ORACLE: - SELECT First Names, removing the space after the first word

Question

ORACLE: - In the table, the first names are as follows

Binda E Reven O Drew J ANDAL J WILL Lee Chad Hardee 

I want to select First names in the following format

 Binda Reven Drew ANDAL WILL Chad 

I use the following query but no luck

 SELECT first_name, SUBSTR(first_name, REGEXP_INSTR('first_name','[^ ]+', 1, 1) ) FROM contact.user_names 

Please offer.

Answer 1

NVL (substr (first_name, 1, instr (first_name, '')), first_name)

 with t AS ( SELECT 'Binda E ' as first_name FROM dual union SELECT 'Reven O ' as first_name FROM dual union SELECT 'Drew J ' as first_name FROM dual union SELECT 'ANDAL J ' as first_name FROM dual union SELECT 'WILL Lee ' as first_name FROM dual union SELECT 'Chad Hardee' as first_name FROM dual union SELECT 'foobar' as first_name FROM dual ) SELECT NVL(substr(first_name, 1, instr(first_name,' ')), first_name) FROM t ;