Well i was given the task to get the email id’s of the user which was stored in one of our oracle db table.
But the problem was that we wanted that part of emailid which appears before ‘@’ .
Well coming from SQL Server background i thought it could be acheived using CharIndex and Left Function.
Let’s see what they do
Select Left(‘abcdef’,3)
-> abc
and
select charindex(‘c’,’abcde’)
-> 3
But than as expected there were no functions like charindex and left in Oracle.
After searching i finally managed to found the solution
Inplace of CharIndex we have instr function
select instr(‘ab’,’b’) from dual;
->2
and for left and also right we have
SUBSTR (`ABCDEF’,-5); //Right(..)
SUBSTR (`ABCDEF’,1,5); // Left(…
So finally the query was
substr(emailid,1,Instr(Emailid,’@’)-1)
-1 is used otherwise @ will also come along
And one more thing, to extract username portion from login name i.e nishantr1 from
abccompany\nishantr1 we could write something as following
SUBSTRING(loginname ,charindex(‘\’,loginname)+1, len(loginname)) for sql server.
Bye