CODEWITHSUNDEEP
sqloracle-database
TNa
TNa

Reputation: 3

How to get first name,last name,provider from a email address in Oracle sql

I try to get first name, last name, provider from an email address "[email protected]" but it doesn't work.

Table emailtable. My code here:

     select 
        SUBSTR(email,1, INSTR(email,'.')-1) as firstname,
        SUBSTR(email,INSTR(email,'.')+1, INSTR(email,'@')-1) as lastname,
        SUBSTR(email,INSTR(email,'@')+1,INSTR(email,'.')-1) as "provider"
        from emailtable;

Upvotes: 0

Views: 2742

Answers (3)

MT0
MT0

Reputation: 168740

The third argument for SUBSTR is the length of the substring (and not the position of the end character).

You can use a nested sub-query to find the position of the separators and then use those values in SUBSTR in the outer query:

SELECT SUBSTR(email, 1, name_separator - 1) AS first_name,
       SUBSTR(email, name_separator + 1, at_separator - name_separator - 1)
         AS last_name,
       SUBSTR(email, at_separator + 1, domain_separator - at_separator - 1)
         AS domain
FROM   (
  SELECT email,
         INSTR(email, '.') AS name_separator,
         INSTR(email, '@') AS at_separator,
         INSTR(email, '.', INSTR(email, '@')) AS domain_separator
  FROM   emailtable
)

Which, for the sample data:

CREATE TABLE emailtable ( email ) AS
SELECT '[email protected]' FROM DUAL;

Outputs:

FIRST_NAME LAST_NAME DOMAIN
ben ghol gmail

db<>fiddle here

Upvotes: 1

NickW
NickW

Reputation: 9818

SUBSTR takes 3 parameters: input string, start position and length of substring.

You appear to be using end position as the 3rd parameter.

You can get the length to use as the 3rd parameter by doing (end position - start position + 1)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use regexp_substr():

select regexp_substr(email, '^[^.]+') as firstname,
       regexp_substr(email, '[^.@]+', 1, 2) as lastname,
       regexp_+substr(email, '[^@]+$') as provider
from (select '[email protected]' as email from dual) x;

Here is a db<>fiddle.

Upvotes: 0

Related Questions