How Do I Get 2 Last Characters from a String in Oracle?

When working with strings in an Oracle database, users may need to extract a certain number of characters from the end of a string. One of the most common cases for character extraction can be extracting the last two characters of the string. These characters are essential in some cases, such as getting the last two digits of an ID number.

This post will provide methods to get the last 2 characters from a string in Oracle:

• Using the SUBSTR Function
• Using the SUBSTR and LENGTH Function

Method 1: Using the SUBSTR Function

The SUBSTR function in Oracle aids in extracting a portion of a string. For instance, a table “CONSUMERS” is considered that contains a “NAME” column having multiple values. To get the last two characters from the “NAME” column values from the table, execute the below command:

The above command takes two arguments, the input string (“NAME” value) and the starting position of the substring (“-2”). However, the negative starting position indicates that the counting will begin from the end of the string for the function. It returns the result in a column named “CONSUMER_STR”.

Output

The output has returned the last two characters of the “NAME” string values in a “CONSUMER_STR” column.

Moreover, the user can also select the “NAME” column to return the result using the “SELECT” statement. It makes the output more readable and understandable. Run the command given below:

The above command returns the “NAME” column and a column named “CONSUMER_LAST_STR” containing the last two characters of the “NAME” string.

Output

The output has returned the “NAME” column and a column “CONSUMER_LAST_STR”.

Method 2: Using the SUBSTR and LENGTH Function

The “LENGTH” function aids in calculating the starting position by utilizing the UTF character set. If the users do not prefer the negative numbers, they can utilize the positive number in the SUBSTR function. The function gives output containing the characters count in the input string. For instance, subtract two characters from the length of the string to get the starting position of the last two characters via the below command:

The above command returns a column named “CONSUMERS_LAST_TWO_CHARS”. It contains the last two characters of the “NAME” column from the “CONSUMERS” table.

Output

The output successfully returned the last two characters of the column named “NAME”.

Conclusion

Oracle offers the SUBSTR function and the combination of SUBSTR and LENGTH functions to get the last 2 characters from a string. The SUBSTR takes two arguments input string and the start position of the portion of the string. While the combination of SUBSTR and LENGTH functions extracts the last two characters from the provided string. This post discusses ways the user can use to extract the last two characters from a string in Oracle.