Extract string without special characters in oracle-CodePudding

I want to extract 'gmail' from [email protected].

I am trying to use

select substr(email,(instr(email,'@') 1),(instr(email,'.')-1) from email

but output coming is

gmail.com

CodePudding user response：

The signature of SUBSTR is not start- and end-positions it is SUBSTR(string, start_position, substring_length):

SELECT SUBSTR(email, at_pos, tld_pos - at_pos) AS result
FROM   (
  SELECT email,
         INSTR(email,'@') 1 AS at_pos,
         INSTR(email,'.',-1) AS tld_pos
  FROM   email
);

or:

SELECT SUBSTR(
         email,
         INSTR(email,'@') 1,
         INSTR(email,'.',-1) - INSTR(email,'@') - 1
       ) AS result
FROM   email;

Which, for the sample data:

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

Both output:

RESULT
example

fiddle

CodePudding user response：

If you're actually trying to fetch substring between the monkey and the dot that follows, how about line #3?

SQL> with email (email) as
  2    (select '[email protected]' from dual)
  3  select ltrim(regexp_substr(email, '@\w '), '@') result
  4  from email;

RESUL
-----
gmail

SQL>