Get The RIGHT Characters

Posted by AgileCoder on June 11, 2015

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 postion, 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;

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



cialis generika kamagra australia cialis preise cialis kaufen cialis bestellen levitra generika viagra online kaufen kamagra bestellen cialis generika kamagra oral jelly kamagra 100mg prix cialis cialis generique kamagra gel viagra prix viagra pour homme acheter cialis kamagra prix viagra pas cher cialis 20mg levitra 20
Levitra Generika kaufen Kaufen Levitra Generika Online Cialis ohne Rezept kaufen Viagra online Viagra Generika kaufen Cialis Soft Tabs kaufen Sildenafil kaufen Apcalis Oral Jelly kaufen Viagra Soft Tabs bestellen Cialis Original kaufen Viagra bestellen Viagra bestellen Propecia Generika kaufen Viagra und seine Analoga Original Testpakete Levitra und Kamagra Erfahrungen Viagra Soft Tabs Kamagra kaufen
kamagra kopen in de winkel viagra werking cialis kopen in nederland cialis bijwerkingen kamagra bijsluiter levitra bijwerkingen viagra kopen apotheek cialis erfaring cialis i norge viagra effekt hva er kamagra viagra nettbutikk levitra eller cialis kamagra gel comprar cialis efeitos secundarios viagra farmacia cialis bula viagra infarmed levitra comprimidos cialis vs viagra cialis flashback kamagra tjejer kamagra effekt levitra fass kamagra oral jelly opiniones levitra generico precio cialis venta kamagra sobres cialis efectos secundarios