Substringing and Oracle SQL – Basic Trick August 18, 2008Posted 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: