CODEWITHSUNDEEP
sqloracle-databaseplsql
Simpson
Simpson

Reputation: 23

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

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.

Upvotes: 2

Views: 4911

Answers (3)

DCookie
DCookie

Reputation: 43533

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);

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332771

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:

Upvotes: 0

Jonathan
Jonathan

Reputation: 12015

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)

Upvotes: 0

Related Questions