Select first name? - Select in 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 to:

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

but with this I only managed to get the first name of those who have surname, already Gaby, Rodrigo and Mario do not appear in the select. Is there any way to return the first name of the surname and show those who have no surname?

Answer 1

From what I understand, you want to show only the names of all the players right? Resulting in 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 names only:

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

^{(read: Locates space, finds everything I’ve been between this space and the first comma I find.)}

So we have the consultation:

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

Answer 2

You can use the NVL

SELECT NVL(SUBSTR(NOMEJOGADOR, 0, INSTR(NOMEJOGADOR, ',')-1), NOMEJOGADOR) AS output
  FROM TABELAJOGADOR 

If you are using Oracle10g+ you can use Regex which is simpler:

SELECT
REGEXP_SUBSTR(NOMEJOGADOR,'(\w+)',1,1) 
FROM TABELAJOGADOR 

Here is the documentation of REGEXP_SUBSTR

Answer 3

First test if there is more than one word in the field, if there is return to the first, if there is no return all content of the field:

    SELECT instr(NOMEJOGADOR, ' ') AS pos_espaco,
           CASE WHEN pos_espaco > 0 THEN
                 substr(NOMEJOGADOR, 0, pos_espaco - 1) 
           ELSE 
                 NOMEJOGADOR
           END AS  primeiro_nome 
     FROM jogador;

Answer 4

Thus:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(nome_da_coluna, ' ', 1), ' ', -1)