Get The RIGHT Characters - AgileCoder.net

Having worked a long time in the Microsoft stack of technologies, it is always a bit jarring when I have to interact with an Oracle database. On function I am constantly trying to use is the T-SQL RIGHT() function. Oracle PL/SQL doesn’t have a RIGHT() function, but I found a workaround in the documentation for the PL/SQL SUBSTR() function that no one else on my team knew about.

Let’s say you have some string (or VARCHAR2 field) and you want to extract the last 5 digits of the string. If you don’t know the length of the string you usually have to write a code that looks like this:

DECLARE 
    testString VARCHAR(100);
    result VARCHAR(100);
BEGIN
    testString := 'abcdefghijklmnop';
    SELECT SUBSTR(testString, LENGTH(testString)-4) 
    INTO result
    FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('Normal SUBSTRING: ' || result);
END;

That SUBSTR() call takes the string, counts all the characters using the LENGTH() function and subtracts 4 to get the starting position in the 0-based array of characters. Since I didn’t pass a third parameter to the function, it returns the rest of the string. The output is the character string ‘lmnop’.

An alternative that is similar in behavior to the RIGHT() function in T-SQL and doesn’t require the call to LENGTH(), or remembering that you have to subtract 1 to adjust the starting position, is to just pass the number of characters you want returned, but as a negative integer:

DECLARE 
    testString VARCHAR(100);
    result VARCHAR(100);
BEGIN
    testString := 'abcdefghijklmnop';
    SELECT SUBSTR(testString, -5) 
    INTO result
    FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('Right() SUBSTRING: ' || result);
END;

This returns the same ‘lmnop’ result.

Where would you ever use this? Well, one example might be dealing with some legacy field or log data where you need to parse out some piece of information that you know is a fixed number of characters from the end of the string. In the example below, we have a log entry that includes some random data then the State Code and Zip Code. If I wanted to extract just the State code I could do it like this:

DECLARE 
    testString VARCHAR(100);
    result VARCHAR(100);
BEGIN
    testString := 'some random length log data ending UT84055';
    SELECT SUBSTR(testString, -7, 2) 
    INTO result
    FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('STATE SUBSTRING: ' || result);
END;

--Outputs
--STATE SUBSTRING: UT

This has come in handy a number of times, so I wanted to write it up so I never forgot it again…