I have been working in an exercise, I have 2 values (one with 9 characters and the other with 8) and I have to show 3 columns.

1. The original number
2. The number without the last character
3. Only the last character

The Query works but I would like to know a way to improve how I get the last column, I used a Case clause but I think there´s a better procedure to accomplish the same thing. Thank you.

select rut, 
   SUBSTR(RUT, 1, LENGTH(RUT)-1) as RUT_SD,
   case when  length(rut) = 9 then 
              SUBSTR(RUT, 9, LENGTH(RUT)-1) 
        when  length(rut) = 8 then
              SUBSTR(RUT, 8, LENGTH(RUT)-1) 
        end as DV

from (select '244447232' rut union all
      select '3446545k')  

CodePudding user response：

Consider below few options

select rut, 

  regexp_extract(rut, r'(. ).$'),
  regexp_extract(rut, r'(.)$'),

  substr(rut, 1, length(rut) - 1),
  substr(rut, -1)

from (
  select '244447232' rut union all
  select '3446545k'
)      

with output