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 

Expert in Oracle Database 10g and 11g. Deep knowledge on SQL, PL/SQL and Java programming. Working at Square InformatiX Limited as Oracle Database Administrator.

Posted in Oracle Tagged with:

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>