Search code examples
sqloracle-databasecase

SQL CASE STATEMENT to last letter in a string

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.

Solution

  • 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

    or

    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.