Tutorial 2 - The Oracle "SUBSTR" Function

Here's the definition of "SUBSTR" according to the Oracle website located at "http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions162.htm".




"The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points."

For most purposes "SUBSTR" is all you'll need, the SUBSTRB, SUBSTR2 and SUBSTR4 are not very common.

Summary: SUBSTR ('length', 'startpositioninstring' )

Here's some syntax if you're just running a simple select:
SELECT SUBSTR('Andrew',3,4) "SOMECOLUMNNAME" FROM SOMETABLE;

Output: drew

Here's some syntax if you're just running a simple select:
SELECT SUBSTR('Andrew',-5,4) "SOMECOLUMNNAME" FROM SOMETABLE;

Output: ndre



Note:
-If position is 0, then it is treated as 1.
-If position is positive, then Oracle Database counts from the beginning of char to find the first character.
-If position is negative, then Oracle counts backward from the end of char.
-If substring_length is omitted, then Oracle returns all characters to the end of char. If substring_length is less than 1, then Oracle returns null.



Applies to :
Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g