Oracle SQL - Function SUBSTR function

Introduction

The SUBSTR function returns part of a string.

The general syntax of the function is as follows:

SUBSTR(original string, begin [,how far])

An original string is to be dissected beginning at the begin character.
If 'how far' is not specified, the rest of the string from the begin point is retrieved.
If begin is negative, retrieval occurs from the right-hand side of original string.

Demo

SQL>
SQL> SELECT SUBSTR('My address is 123 Fourth St.',1,12)
  2  FROM dual;-- from ww  w .ja v  a2 s  .c  o m

SUBSTR('MYAD
------------
My address i

SQL>

Here, the first 12 characters are returned from original string, since begin is 1 and how far is 12.

The blanks count as characters.

Demo

SQL>
SQL> SELECT SUBSTR('My address is 123 Fourth St.',5,12)
  2  From dual;-- from  ww w.  j av a 2s .  c o  m

SUBSTR('MYAD
------------
ddress is 12

SQL>

Here, the retrieval begins at position 5 and again goes for 12 characters.

The following example has no third argument, meaning it starts at begin and retrieves the rest of the string:

Demo

SQL>
SQL> SELECT SUBSTR('My address is 123 Fourth St.',6)
  2  FROM dual;-- from   ww w  .  java2s. c  o  m

SUBSTR('MYADDRESSIS123F
-----------------------
dress is 123 Fourth St.

SQL>

SUBSTR may retrieve from the right-hand side of original string, as shown below:

Demo

SQL>
SQL> SELECT SUBSTR('My address is 123 Fourth St.',-9,5)
  2  FROM dual;--   ww w .  j  a  va 2s . c om

SUBST
-----
ourth

SQL>

The result shows that starting at the right end of the string and counting backward for nine characters, then retrieving five characters from that point.

The following code uses SUBSTR and INSTR together to get the first name.

The last name is of unknown length and we rely only on the format of the names for retrieval, as shown below:

Demo

SQL>
SQL> SELECT SUBSTR('First Name, Last Name', INSTR('First Name, Last Name',', ')+2)
  2  FROM dual;--  w w w.  j  a v  a 2 s.  c  o  m

SUBSTR('F
---------
Last Name

SQL>

The original string is "First Name, Last Name."

The begin number has been replaced by the INSTR function, which returns the position of the comma and blank space.

Since INSTR is using two characters to find the place to begin retrieval, the actual retrieval must begin two characters to the right of that point.

If we do not move over two spaces, then we get this:

Demo

SQL>
SQL> SELECT SUBSTR('First Name, Last Name', INSTR('First Name, Last Name',', '))
  2  FROM dual;-- from  www .j  a  v a2 s .  c  o  m

SUBSTR('FIR
-----------
, Last Name

SQL>

The result includes the comma and space because retrieval starts where the INSTR function indicated the position of search pattern occurred.

If the INSTR pattern is not found, then the entire string would be returned, as shown by this query:

Demo

SQL>
SQL> SELECT SUBSTR('First Name, Last Name', INSTR('First Name, Last Name','ASDF'))
  2  FROM dual;-- from www .  j av  a 2s  .co  m

SUBSTR('FIRSTNAME,LAS
---------------------
First Name, Last Name

SQL>

which is actually this:

Demo

SQL>
SQL> SELECT SUBSTR('First Name, Last Name',0)
  2  FROM dual;-- from www. j  av a2  s . c o  m

SUBSTR('FIRSTNAME,LAS
---------------------
First Name, Last Name

SQL>