How To Use Function Oracle SUBSTR

How To Use Function Oracle SUBSTR

December 12, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This tutorial page is based on examples to make it easier to follow. Functions Oracle SUBSTR, SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4 are to take out a piece of string from the input text and to return the piece that just got cut out. The returned length of the text will depend on your parameters that you entered. The syntax of SUBSTR Oracle is:

SUBSTR(<the input text>,<the starting position>[,<the returning string length>])

The Oracle SUBSTR syntax parameters are:

* “the input text” would be the original text that needs to be cut;

* “the starting position” is a number. When the number is positive then the function will start the count of starting position from left to right. For a negative number vice versa.

* “the returning string length” is an optional number and determines the length of returning text. When the number is smaller than 1 then the Oracle SUBSTR returns empty (null) value.

The first Oracle SUBSTR example will return only the “help” word from the original “computer help” text.

SELECT SUBSTR ('computer help',10)
 FROM dual;

dba oracle dba database management system database oracle performance tuning oracle substr oracle substr in oracle database programming database

The second Oracle select statement returns a string starting from the 10th position and it’s length is 3 characters. The output will be “hel” and the original text is “computer help” again.

SELECT SUBSTR ('computer help',10,3)
  FROM dual;

dba oracle dba database management system database oracle performance tuning oracle substr oracle substr in oracle database programming database

The third Substr example is about the reverse way count. The starting point is from right to left. The function will cut out from right to left 4 characters and it returns a string of 2 characters. The output text will be “he“.

SELECT SUBSTR ('computer help',-4,2)
  FROM dual;

dba oracle dba database management system database oracle performance tuning oracle substr oracle substr in oracle database programming database

The other Oracle SUBSTRx will work in the same way and only the output will be different. There is still exceptional function SUBSTRB that uses bytes instead of character and this function can be used along with Oracle LENGHTB. Since both those Oracle functions working with byte units.

See Also:
Oracle Select Home

Leave a Reply

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

*