sql – Select first name? – Select in the bank

Question:

I have a table with the following names:

João Goldy Alfredo, Gaby, João Antônio, Rodrigo, Maria Aparecida, Mario

How can I get only their first name, and those who don't have a last name, should show it anyway.

what i tried:

 SELECT SUBSTR(NOMEJOGADOR, 0, INSTR(NOMEJOGADOR, ' ', -3))
FROM JOGADOR;

but with that I was only able to get the first name of those who have a surname, since Gaby, Rodrigo and Mario do not appear in the select. Is there any way to return the first name of those with a surname and show those who do not have a surname?

Answer:

As I understand it, you only want to show the name of all players right? Resulting something like this:

João,Gaby,João,Rodrigo,Maria,Mario

If so, first of all, you need to standardize the string, that is, we have space before comma, comma after space.

REPLACE(REPLACE(nomejogador,', ',','),' ,',',')

Then we use regular expression to extract just the names:

[[:blank:]][^,]+

(read: Find the space, find everything that was between that space and the first comma you find.)

So we have the query:

SELECT REGEXP_REPLACE(REPLACE(REPLACE(nomejogador,', ',','),' ,',',')
                     ,'[[:blank:]][^,]+'
                     ,'')
  FROM jogador
Scroll to Top