How to extract the last name from [email protected] using Oracle?

Question

I need to compare the value of a column (LASTNAME) with a system variable (:VARIABLE), but the variable is an email address, so I need to trim off the "@email.com" and "firstname." Some things I've tried:

select * 
  from TABLENAME 
 where LASTNAME LIKE :VARIABLE

select * 
  from TABLENAME 
 where LASTNAME IN :VARIABLE

I've been able to trim off the @email.com, can't figure out how to trim off FIRSTNAME. at the same time.

Answer 1

You can use a combination of SUBSTR and INSTR.

The instr function returns the location of a substring in a string. With this one you can locate the "."or the "@" char.

Then use the substr to get the substring from the begining to the previous located position

SELECT SUBSTR('Take the first four characters', 1, 4)

Answer 2

Regular expressions can help:

SQL> SELECT LTRIM(regexp_substr('[email protected]','\.[^@]*'),'.') last_name from dual;

LAST_NAME
---------
lastname

Your query could then look like:

SELECT *
  FROM tablename 
 WHERE UPPER(LTRIM(regexp_substr(:VARIABLE,'\.[^@]*'),'.')) = UPPER(lastname);

Answer 3

Use:

SELECT t.*
  FROM TABLE t
 WHERE t.lastname LIKE '%' || SUBSTR(:VARIABLE, 
                                     INSTR(:VARIABLE, '.') +1, 
                                     INSTR(:VARIABLE, '@')) || '%'

Add UPPER or LOWER to both sides if you need to be sure of case insensitive matching.

Reference:

• SUBSTR
• INSTR