bnrfly bnrfly - 3 months ago 17
SQL Question

how to select sepecific email domain from email column

I have CUST_TBL with one of the column are EMAIL and USER_ID.

I have many records in CUST_TBL, but I only want to show the record which have specific email domain (like @gmail.com) and user with USER_ID = 'SYSADMIN1'.

I have tried this query

select substr(email,INSTR(email,'@gmail.com'))
from corp_usr
where user_id = 'SYSADMIN1';


But it only shows the email column (I want all column but only with those filter) and still shows the email that's not @gmail.com

What is the correct query for this?

Answer

Just use the INSTR() in the WHERE clause :

select * from  corp_usr 
where user_id = 'SYSADMIN1'
  AND INSTR(email,'@gmail.com') > 0;
Comments