jump to navigation

Substringing and Oracle SQL – Basic Trick August 18, 2008

Posted by Tipster 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)

About these ads

Comments

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

Follow

Get every new post delivered to your Inbox.

Join 395 other followers

%d bloggers like this: