SUBSTR

This example was tested on an Oracle 9 database. Oracle provides a substr function, which allows you to extract part of a string of characters. Often, you will use this on a database column:

SQL> select username from dba_users
  2  where substr(username,1,3) = 'SYS'
  3  /

USERNAME
------------------------------
SYSTEM
SYS

SQL> 

This is equivalent to the following:

SQL> select username from dba_users
  2  where username like 'SYS%'
  3  /

USERNAME
------------------------------
SYSTEM
SYS

SQL> 

But for the remaining examples I will use substr on a fixed character string:

SQL> col substring format a9
SQL> select
  2  substr('International DBA',15,3) substring
  3  from dual
  4  /

SUBSTRING
---------
DBA

SQL> 

As you can see above, the column name or string is followed by 2 parameters, start position and length. Start position tells Oracle where to start in the string and length specifies how many characters to return. The first character in the string is counted as position 1. You can see this in the examples at the start of this post and in the SQL below:

SQL> select
  2  substr('International DBA',1,5) substring
  3  from dual
  4  /

SUBSTRING
---------
Inter

SQL> 

But the first character in the string can be numbered 0 instead:

SQL> select
  2  substr('International DBA',0,5) substring
  3  from dual
  4  /

SUBSTRING
---------
Inter

SQL> 

If the start position is negative, Oracle goes to the end of the string and works backwards to determine where to start from:

SQL> select
  2  substr('International DBA',-3,3) substring
  3  from dual
  4  /

SUBSTRING
---------
DBA

SQL>

But specifying a negative length does not cause the characters to be returned in reverse order:

SQL> select
  2  substr('International DBA',-1,-3) substring
  3  from dual
  4  /

SUBSTRING
---------


SQL>

That is because if length is less than 1, Oracle returns a null:

SQL> select
  2  nvl(substr('Andrew',1,0),'NULL') substring
  3  from dual
  4  /

SUBSTRING
---------
NULL

SQL> select
  2  nvl(substr('Andrew',1,-999),'NULL') substring
  3  from dual
  4  /

SUBSTRING
---------
NULL

SQL>

If you miss out the length, you get all the characters from the starting point to the end of the string:

SQL> select substr('Andrew',4) substring from dual
  2  /

SUBSTRING
---------
rew

SQL>

You can even use expressions for start position and length:

SQL> select
  2  substr('Great Britain',2*2,2*3) substring
  3  from dual
  4  /

SUBSTRING
---------
at Bri

SQL>

And floating point numbers are converted to integers first:

SQL> select substr('Andrew',2.5,3.7) from dual
  2  /

SUB
---
ndr

SQL>