instr, substr

A couple of usefull oracle functions for manipulating strings.

instr(string1, string2, start position, nth appearance) returns the position where the string exists.

where
string1 is the original string to search from
string2 is the string to search in string 1
start posititon is optional and is the start position to start searching from
nth appearance is optional and is the number of occurances of string2 in string1.

instr('Tech on the net', 'e', 1, 3) would return 14.

substr(string1, start position, length) returns a string.

where
string1 is the string to check.
start position is the position in the string to start the substring from
length is optional and the length of the position.

substr('Tech on the net',1,5) would return 'Tech '

Often using a combination of the above functions is useful.

substr('Tech on the net',1, instr('Tech on the net', 'e', 1, 3)) would return ''Tech on the ne'