Substringing and Oracle SQL – Basic Trick August 18, 2008
Posted by Duncan in Oracle, SQL.trackback
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:
SUBSTR(<character_field>,length(<character_field>)-4,4)
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:
SUBSTR(<character_field>,-4)
Comments
Sorry comments are closed for this entry
The PeopleSoft Tipster Blog 
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
Awesome! Thank you! Took me 3 years since it was posted, but I learned something new today.