How To Use Oracle SUBSTR
  Home
 

This tutorial is based on examples to be easier to follow. The functions Oracle SUBSTR, SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4 are to cut out a piece of text from the input string and return it. The returned text length will depend on your parameters that you gave. The SUBSTR oracle syntax is:

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

The oracle substr syntax parameters are following:

* “the input text” would be the original text that needs to be cut out a piece

* “the starting position” is a number. When the number is positive then the substr oracle function will start to count the 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 one then the oracle substr function returns empty (null) value.

The following online tech support computer technician example with the substr oracle function will return only the “help” string from the original “computer help” text.

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

online tech support computer help computer technician computer problems oracle database oracle substr oracle substr in oracle sql substr oracle sql substr in oracle sql database programming database oracle retirement planning retirement

The next online tech support computer help oracle sql select will return a string starting from the 10th position with three characters length. The oracle substr function result will be “hel” from the “computer help” text.

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

online tech support computer help computer technician computer problems oracle database oracle substr oracle substr in oracle sql substr oracle sql substr in oracle sql database programming database oracle retirement planning retirement

The next substr in oracle example is about starting point from right to left. The function will take from right to left four characters and will return text with length of two characters. The online tech support computer technician example will return text as “he”.

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

online tech support computer help computer technician computer problems oracle database oracle substr oracle substr in oracle sql substr oracle sql substr in oracle sql database programming database oracle retirement planning retirement

The oracle substrb uses bytes instead of character and this function can be used with oracle lengthb. Both Oracle functions are working with bytes.



See Also:
Oracle Select Oracle INSTR Oracle Decode

 Leave a Reply

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=""> <s> <strike> <strong>

(required)

(required)

*