CODEWITHSUNDEEP
sqloracle-databasesubstrcase-when
Laura
Laura

Reputation: 139

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

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.

Upvotes: 1

Views: 470

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

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.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142788

Sample data in lines #1 - 5; code that does the job begins at line #6.

SQL> with test (elcode) as
  2    (select 'AM1234' from dual union all
  3     select 'AR1357' from dual union all
  4     select 'PD9876' from dual
  5    )
  6  select elcode,
  7    case when substr(elcode, 2, 1) = 'M' then 'mammal'
  8         when substr(elcode, 2, 1) = 'R' then 'reptile'
  9         when substr(elcode, 1, 1) = 'P' then 'plant'
 10    end taxa_group
 11  from test;

ELCODE TAXA_GROUP
------ ----------
AM1234 mammal
AR1357 reptile
PD9876 plant

SQL>

Upvotes: 2

Related Questions