Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table geography as
select null as id
  union all
select 'TR' 
  union all
select 'MN'
  union all
select 'AS'
  union all
select 'NL'
  union all
select 'AN';
6 rows affected

SELECT ID,CASE ID
WHEN 'TR' THEN 'TRIPURA'
WHEN 'MN' THEN 'MANIPUR'
WHEN 'AS' THEN 'ASSAM'
WHEN 'NL' THEN 'NAGALAND'
WHEN 'AN' THEN 'ARUNACHAL'
WHEN NULL THEN 'NO STATE'
ELSE 'OTHER STATE'
END AS STATE
FROM GEOGRAPHY;


ID
STATE


null
OTHER STATE

TR
TRIPURA

MN
MANIPUR

AS
ASSAM

NL
NAGALAND

AN
ARUNACHAL


SELECT ID,
  CASE WHEN ID IS NULL THEN 'NO STATE'
  ELSE CASE ID
  WHEN 'TR' THEN 'TRIPURA'
  WHEN 'MN' THEN 'MANIPUR'
  WHEN 'AS' THEN 'ASSAM'
  WHEN 'NL' THEN 'NAGALAND'
  WHEN 'AN' THEN 'ARUNACHAL'
  ELSE 'OTHER STATE'
END END AS STATE
FROM GEOGRAPHY;


ID
STATE


null
NO STATE

TR
TRIPURA

MN
MANIPUR

AS
ASSAM

NL
NAGALAND

AN
ARUNACHAL

ID	STATE
null	OTHER STATE
TR	TRIPURA
MN	MANIPUR
AS	ASSAM
NL	NAGALAND
AN	ARUNACHAL

ID	STATE
null	NO STATE
TR	TRIPURA
MN	MANIPUR
AS	ASSAM
NL	NAGALAND
AN	ARUNACHAL