Oracle SUBSTR Function

Oracle SUBSTR (SubString | Sub String) function lets you extract a portion of string from a given string.

Syntax of Oracle SUBSTR function:

SUBSTR(string, position [, length ])


Parameter Description of Oracle SUBSTR Function:

Required parameter string can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.

Required parameter position must be of datatype NUMBER or any datatype that can be implicitly converted to NUMBER.

Optional parameter length must be of datatype NUMBER or any datatype that can be implicitly converted to number.

Floating-point numbers passed as parameters value to Oracle SUBSTR function are implicitely converted to integers.

If position is 0 then it is treated as 1.

If position is positive the Oracle counts from the beginning of string to find the first character otherwise counts backword from the end of the character.
Return Value of Oracle SUBSTR Function:

Return value of Oracle SUBSTR Function is char datatype.

If length parameter is omitted then Oracle returns all characters to the end of string.

If length parameter is less than 1 then Oracle returns null.

Example of Oracle SUBSTR Function:

SQL> SELECT SUBSTR('Apple introduce iPad', 1, 5) AS OUTPUT
  2  FROM   DUAL;

OUTPU                                                                           
-----                                                                           
Apple                                                                           

SQL> SELECT SUBSTR('Apple introduce iPad', 1) AS OUTPUT
  2  FROM   DUAL;

OUTPUT                                                                          
--------------------                                                            
Apple introduce iPad                                                            

SQL> SELECT SUBSTR('Apple introduce iPad', 1, -1) AS OUTPUT
  2  FROM   DUAL;

O                                                                               
-                                                                               
                                                                                

SQL> SELECT SUBSTR('Apple introduce iPad', 1.12F, 5) AS OUTPUT
  2  FROM   DUAL;

OUTPU                                                                           
-----                                                                           
Apple                                                                           

SQL> SELECT SUBSTR('Apple introduce iPad', 1.12F, 5.99) AS OUTPUT
  2  FROM   DUAL;

OUTPU                                                                           
-----                                                                           
Apple                                                                           

SQL> SELECT SUBSTR('Apple introduce iPad', 1.12F, 7.99) AS OUTPUT
  2  FROM   DUAL;

OUTPUT                                                                          
-------                                                                         
Apple i 

Rahinur Rahaman is an Oracle Database Administrator. Currently he is working at Square InformatiX Limited, Dhaka, Bangladesh.

Tagged with:
Posted in Oracle

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>