Character Functions

1. SUBSTR

    Syntax:    SUBSTR( string, position, length );

    ex:        Select substr( 'shashi', 2) from dual;    -- hashi
            Select substr( 'shashi', 0, 1) from dual;    -- s
            Select substr( 'shashi', 1, 1) from dual;    -- s
            Select substr( 'shashi', 0, 2) from dual;    -- sh
            Select substr( 'shashi', -1, 1) from dual;    -- i
            Select substr( 'shashi', -2, 2) from dual;    -- hi
            Select substr( 'shashi', -2, 6) from dual;    -- hi    only remaining characters are printed.
   
    Logic:    length is optional in SUBSTR.
                If length is not provided, starting from the position, entire string will be printed.
            If position = -ve, Oracle starts from backward.
            If position is greater than the length, nothing is printed.
            If position = -ve and length > remaining characters, Only the string is printed but not the null values.
           

2. INSTR

    Syntax:    INSTR( String, search_string, start, occurance );

    ex:        Select instr( 'shashi', 'h') from dual;        -- 2
            Select instr( 'shashi', 's') from dual;        -- 1
            Select instr( 'shashi', 's',2) from dual;    -- 4
            Select instr( 'shashi', 'h',2) from dual;    -- 5
            Select instr( 'shashi', 's',1,2) from dual;    -- 4
            Select instr( 'shashi', 's',-1,2) from dual;    -- 1
            Select instr( 'shashi', 'h',1,2) from dual;    -- 5
            Select instr( 'shashi', 's',6,2) from dual;    -- 0   
   
    Logic:    start, occurance are optional in INSTR.
                If start is not provided, it starts from the begging.
                If occurance is not provided, it returns the first occurance.
            If start is -ve, Oracle starts from backward.

3. REPLACE

    Syntax:    REPLACE( String, search_string, replacement_string );

    ex:        Select replace('shashi', 'h') from dual;        -- sasi
            Select replace('shashi', 'h', 'o') from dual;        -- soasoi
   
    Logic:    replacement_string is optional.

4. TRANSLATE

    Syntax:    TRANSLATE( 'expression', 'fromString', 'toString');

    ex:        Select translate('shashikanth', 'ha', 'mn') from dual;        -- smnsmiknntm
            Select translate('shashikanth', 'shas', 'kota') from dual;    -- kotkoiktnto        here s = { k,a }, but s accepts only k.

Logic:    toString is optional.
                If toString is not provided, blank characters are placed in place of fromString characters.
            It is a character based replacement.   
                If a character is assigned a value, it acts as a constant value.