Oracle String/Char Function - Oracle/PLSQL SUBSTR Function

This Oracle tutorial explains how to use the Oracle/PLSQL SUBSTR function.

The Oracle/PLSQL SUBSTR functions extracts a substring from a string.

SUBSTR retrieves a portion of the string. The general format for this function is:

SUBSTR(string, start_at_position[, number_of_characters_to_retrieve])

Syntax

The syntax for the Oracle/PLSQL SUBSTR function is:

SUBSTR( string, start_position, [ length ] )

string is the source string.

start_position is the position for extraction. The first position in the string is always 1.

length is optional. It is the number of characters to extract. Default to the entire string.

If start_position is 0, then the SUBSTR function treats start_position as the first position in the string.
If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.
If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.
If length is a negative number, then the SUBSTR function will return a NULL value.

Example


SQL> SELECT SUBSTR('ThisIsATest', 5, 3) FROM dual;

SUB
---
IsA

SQL>

The effect of starting point:


SQL> SELECT SUBSTR('My address is 123 Main St.',1,12) FROM dual;
-- w ww . j ava2  s .  co  m
SUBSTR('MYAD
------------
My address i

SQL> SELECT SUBSTR('My address is 123 Main St.',5,12) From dual;

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

SQL>

If no number_of_characters_to_retrieve is specified, then the rest of the string from the begin point is retrieved.


SQL> SELECT SUBSTR('ThisIsATest', 5) FROM dual;

SUBSTR(
-------
IsATest

SQL>
SQL>

Negative Start Position

If start_at_position is negative then the retrieval starts from the right-hand side the the string.


SQL> SELECT SUBSTR('My address is 123 Main St.',-1,12) FROM dual;
-- w w  w .  j  a  v  a  2  s .  c o  m
S
-
.

SQL> SELECT SUBSTR('My address is 123 Main St.',-5,12) From dual;

SUBST
-----
n St.

SQL>

Using SUBSTR and INSTR together.


SQL> SELECT SUBSTR('FirstName, Last', INSTR('FirstName, Last',', ')+2) FROM dual;

SUBS
----
Last

SQL>

Combine SUBSTR and LOWER


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
--  ww w. j  a  va2s . co m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> select substr(lower(ename), 2,1) from emp;

S
-
m
l
a
o
a
l
l
c
i
u
d

11 rows selected.

You can combine any valid functions together.