How To Use Function Oracle SUBSTR

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;

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;

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;

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