Using Left and CharIndex in Oracle

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