Oracle SQL Developer - SUBSTR and LIKE

Question

I have a column NAME_SURNAME (made of names and surnames) from a table people:

John Smith
Tim Burton
Harry Potter

I need to write a query to obtain only the names:

John
Tim
Harry

I am trying in this way nad it doesn't work:

select NAME_SURNAME, substr(NAME_SURNAME, 1, LIKE '% %')
from PEOPLE

I don't figure out how to use LIKE in this exercise.

Thank you really much

Answer 1

Use INSTR with SUBSTR to take a substring up to the first space:

SELECT NAME_SURNAME, SUBSTR(NAME_SURNAME, 1, INSTR(NAME_SURNAME, ' ') - 1)
FROM PEOPLE;

Demo

For a regex approach, we can use REGEXP_SUBSTR:

SELECT NAME_SURNAME, REGEXP_SUBSTR(NAME_SURNAME, '^\S+') AS FIRST_NAME
FROM PEOPLE;