Get substring of length 1 or 2 for values in a column and put in single column in output?

Question

I have a column that contains an alphanumeric code (elcode). I'm trying to create a new column in my select output that contains a value based on either the first one or two characters of the code field using SUBSTR() and CASE WHEN (TAXA_GROUP), but I can't figure out how to do this such that all the CASE WHEN results are in a single column.

Example: Three example codes: AM1234, AR1357, PD9876. For the first two, I need to look at the first two characters. The first one would translate to 'mammal' in my output and the second one to 'reptile.' For the last one, I only need to look at the first character, which would translate to 'plant' in my output. In the whole dataset, there are 5 two-character substrings and 4 one-character substrings I need to evaluate.

I can produce something that contains two separate columns, one with the values resulting from evaluating those two-character substrings and the other with the values resulting from the one-character substrings, but I really want them all to go in the same final column. Here is what I'm doing so far:

SELECT EG.elcode
, CASE SUBSTR(EG.elcode, 1, 2) WHEN 'AM' THEN 'mammal'
WHEN 'AR' THEN 'reptile' END AS TAXA_GROUP
, CASE SUBSTR(EG.elcode, 1, 1) WHEN 'P' THEN 'plant' END AS TAXA_GROUP

The above produces output with two TAXA_GROUP columns, one populated with mammal, reptile, and null and the other populated with plant and null:

elcode	TAXA_GROUP	TAXA_GROUP
AM1234	mammal	null
AR1357	reptile	null
PD9876	null	plant

What I would like is to have one TAXA_GROUP column with the results of both of those SUBSTR() commands together.

elcode	TAXA_GROUP
AM1234	mammal
AR1357	reptile
PD9876	plant

I tried concatenating the two columns, but I get an error:

SELECT EG.elcode
, CASE SUBSTR(EG.elcode, 1, 2) WHEN 'AM' THEN 'mammal'
WHEN 'AR' THEN 'reptile' END AS TAXA_GROUP1
, CASE SUBSTR(EG.elcode, 1, 1) WHEN 'P' THEN 'plant' END AS TAXA_GROUP2
, TAXA_GROUP1 || TAXA_GROUP2 AS TAXA_GROUP
Error: An error occurred while running query. ORA-00904: "TAXA_GROUP2": invalid identifier 

In my searching on this site, I found similar questions, but most related to using SUBSTR() when you don't know how long the string is (requiring regex), which is not the case here.

Answer 1

I would use LIKE. It is simpler:

select elcode,
       (case when elcode like 'AM%' then 'mammal'
             when elcode like 'AR%' then 'reptile'
             when elcode like 'P%'  then 'plant'
        end) taxa_group
from t;

But the point is that you want separate case conditions rather than just comparing one value.