SQL CASE STATEMENT to last letter in a string-CodePudding

I have a column with the following values:

column_1
1223B
123C
028409d
abce
ABCf

I want to write a case statement that takes a value of 1 if the last value in the string is lowercase, otherwise 0.

Any suggestions how I can approach this?

Edit #1:

The only values that will be found in this column are numbers and letters.

Edit #2:

The last character of the string will always be a letter.

CodePudding user response：

Depending on how you want to handle string that don't end with an uppercase/lowercase character, you could do:

case when substr(column_1, -1) = lower(substr(column_1, -1)) then 1 else 0 end

case when substr(column_1, -1) != upper(substr(column_1, -1)) then 1 else 0 end

db<>fiddle

The substr(column_1, -1) gives you the last character; from the documentation:

If position is negative, then Oracle counts backward from the end of char.

You can then compare that with the the lower(...) (or upper) of that and see it if matches.

You could also use a regular expression but that doesn't seem necessary here.

CodePudding user response：

You can combine LOWER() with SUBSTR() to find if the last char is lower case. For example:

select c, 
  case when lower(substr(c, -1, 1)) = substr(c, -1, 1) 
       then 1 else 0 end as f
from (
  select '1223B' as c from dual
  union all select '123C' from dual
  union all select '028409d' from dual
  union all select 'abce' from dual
  union all select 'ABCf' from dual
) x

Result:

 C        F 
 -------- - 
 1223B    0 
 123C     0 
 028409d  1 
 abce     1 
 ABCf     1

See example at db<>fiddle.

CodePudding user response：

Or:

SQL> with test (col) as
  2    (select '1223B'   from dual union all
  3     select '123C'    from dual union all
  4     select '028409d' from dual union all
  5     select 'abce'    from dual union all
  6     select 'ABCf'    from dual union all
  7     select '1234'    from dual
  8    )
  9  select col,
 10         case when regexp_substr(col, '[[:alpha:]]$') = lower(substr(col, -1)) then 1
 11              else 0
 12         end result
 13  from test;

COL         RESULT
------- ----------
1223B            0
123C             0
028409d          1
abce             1
ABCf             1
1234             0

6 rows selected.

SQL>