jump to navigation

Substringing and Oracle SQL – Basic Trick August 18, 2008

Posted by Duncan in Oracle, SQL.

This may well be something that you already know, but it was totally new to me.  It was one of those magic moments where you stumble across something so elementary you wonder:

a) how you haven’t found out about it before

b) how much time it’ll save in the future

This is related to taking only a portion of a string using Oracle SQL, and in particular the right hand side of a string.  In other programming languages I’ve been spoilt with the RIGHT(x,num_chars) command, which we don’t have in Oracle.

In the past I’ve made do using something like:


if I wanted to take the 4 right-most characters from a string.  This gets cumbersome pretty quickly when you have multiple substrings etc.

Today – to my delight – I discovered that you can include negative numbers and Oracle will count back from the right hand side.  So this will take the 4 right-most characters from a string:




1. Dan - August 19, 2008

In playing with this, noticed that if you use a number greater the the length of the string it will return null.

e.g., SUBSTR(‘Daniel’,-7) -> null

2. Ian - December 6, 2011

Awesome! Thank you! Took me 3 years since it was posted, but I learned something new today.

Sorry comments are closed for this entry

%d bloggers like this: