Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE data (lhs CHAR(2), rhs CHAR(1));
INSERT INTO data VALUES ('a ', 'a');
1 rows affected

select case when lhs > rhs then 't' else 'f' end AS gt_ch,
       case when lhs < rhs then 't' else 'f' end AS lt_ch,
       case when lhs = rhs then 't' else 'f' end AS eq_ch,
       case
       when lhs > rhs then 'gt'
       when lhs < rhs then 'lt'
       when lhs = rhs then 'eq'
       else NULL
       end AS comp,
       case
       when CAST(lhs AS VARCHAR2(10)) > CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS gt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) < CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS lt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) = CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS eq_vc,
       DUMP(lhs) AS dmp_lsh_raw,
       DUMP(CAST(lhs AS CHAR(2))) AS dmp_lhs_ch,
       DUMP(CAST(lhs AS VARCHAR2(10))) AS dmp_lhs_vc,
       DUMP(rhs) AS dmp_rsh_raw,
       DUMP(CAST(rhs AS CHAR(2))) AS dmp_rhs_ch,
       DUMP(CAST(rhs AS VARCHAR2(10))) AS dmp_rhs_vc
from   data;


GT_CH
LT_CH
EQ_CH
COMP
GT_VC
LT_VC
EQ_VC
DMP_LSH_RAW
DMP_LHS_CH
DMP_LHS_VC
DMP_RSH_RAW
DMP_RHS_CH
DMP_RHS_VC


f
f
t
eq
t
f
f
Typ=96 Len=2: 97,32
Typ=96 Len=2: 97,32
Typ=1 Len=2: 97,32
Typ=96 Len=1: 97
Typ=96 Len=2: 97,32
Typ=1 Len=1: 97


WITH cte (lhs, rhs) AS (
  SELECT CAST('a ' AS CHAR(2)),
         CAST('a' AS CHAR(1))
  FROM   DUAL
)
select case when lhs > rhs then 't' else 'f' end AS gt_ch,
       case when lhs < rhs then 't' else 'f' end AS lt_ch,
       case when lhs = rhs then 't' else 'f' end AS eq_ch,
       case
       when lhs > rhs then 'gt'
       when lhs < rhs then 'lt'
       when lhs = rhs then 'eq'
       else NULL
       end AS comp,
       case
       when CAST(lhs AS VARCHAR2(10)) > CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS gt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) < CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS lt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) = CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS eq_vc,
       DUMP(lhs) AS dmp_lsh_raw,
       DUMP(CAST(lhs AS CHAR(2))) AS dmp_lhs_ch,
       DUMP(CAST(lhs AS VARCHAR2(10))) AS dmp_lhs_vc,
       DUMP(rhs) AS dmp_rsh_raw,
       DUMP(CAST(rhs AS CHAR(2))) AS dmp_rhs_ch,
       DUMP(CAST(rhs AS VARCHAR2(10))) AS dmp_rhs_vc
from   cte;


GT_CH
LT_CH
EQ_CH
COMP
GT_VC
LT_VC
EQ_VC
DMP_LSH_RAW
DMP_LHS_CH
DMP_LHS_VC
DMP_RSH_RAW
DMP_RHS_CH
DMP_RHS_VC


f
f
t
eq
t
f
f
Typ=96 Len=2: 97,32
Typ=96 Len=2: 97,32
Typ=1 Len=2: 97,32
Typ=96 Len=1: 97
Typ=96 Len=2: 97,32
Typ=1 Len=1: 97


WITH cte_default_data_type (lhs, rhs) AS (
  SELECT 'a ',
         'a'
  FROM   DUAL
)
select case when lhs > rhs then 't' else 'f' end AS gt_ch,
       case when lhs < rhs then 't' else 'f' end AS lt_ch,
       case when lhs = rhs then 't' else 'f' end AS eq_ch,
       case
       when lhs > rhs then 'gt'
       when lhs < rhs then 'lt'
       when lhs = rhs then 'eq'
       else NULL
       end AS comp,
       case
       when CAST(lhs AS VARCHAR2(10)) > CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS gt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) < CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS lt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) = CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS eq_vc,
       DUMP(lhs) AS dmp_lsh_raw,
       DUMP(CAST(lhs AS CHAR(2))) AS dmp_lhs_ch,
       DUMP(CAST(lhs AS VARCHAR2(10))) AS dmp_lhs_vc,
       DUMP(rhs) AS dmp_rsh_raw,
       DUMP(CAST(rhs AS CHAR(2))) AS dmp_rhs_ch,
       DUMP(CAST(rhs AS VARCHAR2(10))) AS dmp_rhs_vc
from   cte_default_data_type;


GT_CH
LT_CH
EQ_CH
COMP
GT_VC
LT_VC
EQ_VC
DMP_LSH_RAW
DMP_LHS_CH
DMP_LHS_VC
DMP_RSH_RAW
DMP_RHS_CH
DMP_RHS_VC


f
f
f
eq
t
f
f
Typ=96 Len=2: 97,32
Typ=96 Len=2: 97,32
Typ=1 Len=2: 97,32
Typ=96 Len=1: 97
Typ=96 Len=2: 97,32
Typ=1 Len=1: 97