Solved

# Extract emails based on domain on another table.

Posted on 2005-04-10
240 Views
DB: Access 2002.
I only have two simple tables.

Table A
- email  [string]

Table B
- domain [string]

----------------------------------
Table A Sample Data
- abc@hotmail.com
- abc@NotThisDomain.com
- abc@yahoo.com
- abc@NotThisToo.com
- abc@yahoo.com.au
----------------------------------
Table B Sample Data
- hotmail.com
- yahoo.com
----------------------------------

I want a method (Query or function call to create new table) to extract the listed Domains in Table B out from Table A.

Wanted Result Table
- abc@hotmail.com
- abc@yahoo.com
- abc@yahoo.com.au

0
Question by:dennisdominic

LVL 77

Expert Comment

Hi dennisdominic,

Select * from tableA
Where mid([email], instr([email],"@")+1) in (Select Domain from tableB)

Pete
0

LVL 19

Accepted Solution

Try this:

MS Access version:
SELECT A.email
FROM A INNER JOIN B ON A.email LIKE ('*@' + B.domain + '*');

MS SQL (and other DBs):
SELECT A.email
FROM A INNER JOIN B ON A.email LIKE ('%@' + B.domain + '%');

0

LVL 1

Author Comment

On my own table.... Method from peter57r returns 81 rows and method from Rimvis returns 259 rows.

My own ASP Script below returns 399 rows.

<%
set rs1 = conn.execute("Select Domain from TableB")

while not rs1.eof
sqlstr = "select email from TableA where email like '%@%" & rs1("Domain") & "%'"
set rs2 = conn.execute(sqlstr)

while not rs2.eof
sqlstr="Insert INTO ResultTable (newEmail) values ('" & rs2("email") & "')"
conn.execute(sqlstr)
rs2.movenext
wend

rs1.movenext
wend
%>
0

LVL 19

Expert Comment

Ther's an erros in your script
'%@%" & rs1("Domain") & "%'" - extra '%' after @
Should be
'%@" & rs1("Domain") & "%'" - extra '%' after @
0

LVL 19

Expert Comment

Oops
Should be
'%@" & rs1("Domain") & "%'"
0

LVL 1

Author Comment

I can email the entire database (10mb) to you if you drop me an email at dennisdominc @ gmail . com

Hoping for a quick and dirty SQL solution that I can implement on Query
or any ACCESS solution.

0

LVL 19

Expert Comment

Check result with your corrected script.
This should work:
SELECT A.email
FROM A INNER JOIN B ON A.email LIKE ('%@' + B.domain + '%');

BTW, question related communication via e-mail is against Member Agreement
0

LVL 1

Author Comment

'%@" & rs1("Domain") & "%'"
- 259... Cool... you are right. :-)

'%@%" & rs1("Domain") & "%'"
- 399 rows... I was hoping to capture those abc@uk.yahoo.com abc@yahoo.com.uk and all variations..

0

LVL 19

Expert Comment

>>  I was hoping to capture those abc@uk.yahoo.com abc@yahoo.com.uk and all variations
Last % DOES it.
0

LVL 1

Author Comment

you need to fit a % (or *)  between @ and yahoo.com for uk. to go in...

don't email me :-p I didn't realise the rules.
0

LVL 19

Expert Comment

Sorry, didn't got it, I was thinking about yahoo.com.uk.
In this case, you ARE right. Should be

SELECT A.email
FROM A INNER JOIN B ON A.email LIKE ('%@%' + B.domain + '%');
0

## Featured Post

### Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…