Filtering for Domains in an email address

building semantic layer
1

I have a query which finds Customers( Companies) in the Customer Table and links to the COntact file which has contacts and email addresses. (There are no email addresses or domain names in the Customer table.)

I would like to strip out the domain names from the email addresses to create a report of Customer and domain name.

Need some help.

I have the query producing the Customers and Contact email address, just not sure how to strip it down to the domain.

Thanks

johnlf (BOB member since 2007-10-04)

2

Hi, Welcome to B:bob:B!

What database are you using?

What you need to do is find the @ in the email address, and take everything following that.
Oracle pseudo code would be:

substring(email_address, instr(email_address, '@')+1, length(email_address))

MySQL offers the substring_index() function which serves the same purpose as the Oracle instr() function. SQL Server offers charindex(), Teradata gives us position()… most databases will offer some way to look inside of a string and find a character or other string and its position.

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Thanks Dave,

I’m using Oracle. I’ll try this during this afternoon.

johnlf (BOB member since 2007-10-04)

4

Here is a link to a page that details some of the uses of the substr() and instr() string functions available in Oracle:

http://www.psoug.org/reference/substr_instr.html

I think the Oracle code (real code this time :wink: ) would be:

substr(table.email_address, instr(table.email_address, '@')+1)

You don’t need to provide the length of the string, if you leave it out the substr() function operates from the starting position you gave it and goes to the end of the string.

Dave Rathbun :us: (BOB member since 2002-06-06)