Substr,Length of Str in Oracle

This is the trickest I think I have ever been thru... hmm I kind of hated it as the string in Oracle starts from 1 and not from 0.

here is an example:

l_s_mystring varchar2(20):='suneetha';

l_s_firstString:=substr(l_s_mystring,1,1);

l_s_firstString1:=substr(l_s_mystring,3,1);

l_s_firstString2:=substr(l_s_mystring,5,1);

will print s,n,e etc

here is how it processes

S U N E E T H A
1 2 3 4 5 6 7 8

So when u ask for substr(l_s_mystring,1,1) will print s

when u ask for substr(l_s_mystring,1,3) will print SUN (1 AND 3 INCLUDED)

this sucks and I totally hate it grrrrr!!!!!

So dont forget this from next time onwards :)


Function to get the length of the string. This is soo different than in Java
so had to make a note :)

select length(to_char('suneetha')) from dual;

syntax: length()