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.