Select first name?

Navigation
2

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 them to get their first names, and those who do not have a last name, should show anyway.

What I've tried: 

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

But with this I only managed to get the first name of those who have a surname, since Gaby, Rodrigo and Mario do not appear in the select. Is there a way to return the first names of the last names and display the ones that do not have a last name?

    
asked by anonymous 27.06.2018 / 01:08

4 answers

1

From what I understand, you want to show only the name of all the players right? Something like this: 

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

If this is, 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 only the names: 

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

(read: Find the space, find everything I've been between these spaces and the first comma I find.)

So we have the query: 

SELECT REGEXP_REPLACE(REPLACE(REPLACE(nomejogador,', ',','),' ,',',')
                     ,'[[:blank:]][^,]+'
                     ,'')
  FROM jogador
    
28.06.2018 / 20:11
0

First test if there is more than one word in the field, if it exists, return the first one, if it does not exist, return all the contents 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;
    
28.06.2018 / 20:04
0

You can use 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 you have the documentation for REGEXP_SUBSTR

    
28.06.2018 / 19:56
-1

So: 

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(nome_da_coluna, ' ', 1), ' ', -1)
    
27.06.2018 / 01:22
NPM x NPM Orgs x NPM Enterprise Error @DatabaseField + Abstract class ORMLite (java.lang.IllegalArgumentException: No fields have a DatabaseField annotation in class ***. Object)