How to exclude everything after an @ in SQL?

Question

I'm new to sql and I'm writing a sql script to select data and the results will be displayed in a .csv format. For one of the fields, I'm needing to select only what is before the @ in the email address for everyone in the listing. I'm not wanting to update the records in the table.

Ex: john.doe@yahoo.com

I'm only needing to select the john.doe

I need assistance please in doing this. I'm using sqlplus in a Linux environment.

Here's now what I have as part of the script. I still need assistance with getting the desired output.

(select nvl(c.email_email_address, ' ')

    from email c, person a
    where c.email_pidm = a.person_pidm and
          PERSON.ID = a.person_id and
          c.email_emal_code = 'EMPL' and
          c.email_status_ind = 'A' and
          c.rowid = (select max(b.rowid)
                                   from email b
                                   where b.email_pidm = a.person_pidm and
                                         b.email_emal_code = 'EMP'
                                   and b.email_status_ind = 'A')
                                                                    ) "Employee_Email_Address",
SELECT SUBSTR(email_email_address, 0, INSTR(email_email_address, '@')-1) 
--(select nvl(c.email_email_address, ' ')
        from email c, person a
        where c.email_pidm = a.person_pidm and
              PERSON.ID = a.person_id and
              c.email_emal_code = 'EMP' and
              c.email_status_ind = 'A' and
              c.rowid = (select max(b.rowid)
                                       from email b
                                       where b.email_pidm = a.person_pidm and
                                             b.email_emal_code = 'EMPL'
                                       and b.email_status_ind = 'A')
                                                                        ) "Username"

score 0 · Accepted Answer · answered Mar 30 '15 at 19:41

0

SELECT SUBSTR('test@example.com', 1, INSTR('test@example.com', '@')-1) FROM DUAL;

Output:

test

answered Mar 30 '15 at 19:41

user700390

2,287
1
19
26

score 0 · Answer 2 · answered Mar 30 '15 at 19:42

0

You could do:

select SUBSTR("test@testdomain.com", 1, INSTR("test@testdomain.com", "@")-1)
from dual

answered Mar 30 '15 at 19:42

grungero

81
4

Chains · Answer 3 · 2015-03-30T19:49:48.350

0

SELECT SUBSTR(FIELD_NAME, 0, INSTR(FIELD_NAME, '@')-1) FROM TABLE_NAME

SUBSTR works like this:

SUBSTR( string, start_position, [ length ] )

The length parameter is what ...INSTR(FIELD_NAME, '@')-1... is putting in for you.

INSTR tells you where in the string (or field) the character you're looking or is found. You have to -1 that result, so the '@' doesn't show-up in your return value.

edited Mar 30 '15 at 19:49

answered Mar 30 '15 at 19:43

Chains

12,541
8
45
62

Although the DB engine will automatically convert the 0 to a 1 for you, it is probably worth noting that in Oracle, strings are 1-based not 0-based. – user700390 Mar 30 '15 at 19:44

How to exclude everything after an @ in SQL?

3 Answers3