Error using ALIAS in select

Question

In the select below, I would like to use the alias novonome in the second column (instr).

select 
    substr(titulo, 1, 20) AS **novonome**,
    instr(**novonome**, ' ')+1
from conteudo;

But I get the mistake:

ORA-00904: "novonome": invalid identifier

Answer 1

You actually have two problems; the first one causes an error in execution because the alias is set between two asterisks instead of between quotation marks:

select substr(titulo, 1, 20) AS 'novonome' 
from conteudo;

The second is that you cannot use the value returned by alias as you intend, you would have to play the same function call to return the second column:

select
  substr(titulo, 1, 20) AS 'novonome',
  instr(substr(titulo, 1, 20), ' ') + 1
from conteudo;