CASE .. WHEN expression in Oracle SQL

You could use an IN clause

Something like

SELECT  status,  CASE    WHEN STATUS IN('a1','a2','a3')    THEN 'Active'    WHEN STATUS = 'i'    THEN 'Inactive'    WHEN STATUS = 't'    THEN 'Terminated'  END AS STATUSTEXTFROM  STATUS

Have a look at this demo

SQL Fiddle DEMO

Of course...

select case substr(status,1,1) -- you're only interested in the first character.            when 'a' then 'Active'            when 'i' then 'Inactive'            when 't' then 'Terminated'       end as statustext  from stage.tst

However, there's a few worrying things about this schema. Firstly if you have a column that means something, appending a number onto the end it not necessarily the best way to go. Also, depending on the number of status' you have you might want to consider turning this column into a foreign key to a separate table.

Based on your comment you definitely want to turn this into a foreign key. For instance

create table statuses ( -- Not a good table name :-)    status varchar2(10)  , description varchar2(10)  , constraint pk_statuses primary key (status)    )create table tst (    id number  , status varchar2(10)  , constraint pk_tst primary key (id)  , constraint fk_tst foreign key (status) references statuses (status)    )

Your query then becomes

select a.status, b.description  from tst a  left outer join statuses b    on a.status = b.status

Here's a SQL Fiddle to demonstrate.

You can rewrite it to use the ELSE condition of a CASE:

SELECT status,       CASE status         WHEN 'i' THEN 'Inactive'         WHEN 't' THEN 'Terminated'         ELSE 'Active'       END AS StatusTextFROM   stage.tst