CODEWITHSUNDEEP
sqlemailgoogle-bigquery
user3726904
user3726904

Reputation: 41

How to extract Email Domain from Email Address within Google Big Query

I've got a load of customer data and I'm trying to find a way within Big Query to extract the email domain (so yahoo.com out of [email protected] for example) however any attempts I've made using normal SQL doesn't seem to work as they don't support CHARINDEX while any substrings can only be created based on a numeric value.

Does anyone have any ideas on how I could do this?

Cheers,

Matt

Upvotes: 4

Views: 14402

Answers (4)

dok
dok

Reputation: 21

I think this is the best option if you don't want ".com"

SELECT REGEXP_EXTRACT("[email protected]", "@(\\w+)") AS domain;

Will give you only "yahoo"

Upvotes: 0

evan_b
evan_b

Reputation: 1239

This is by far the easiest, I think:

SPLIT(email_address, '@')[SAFE_OFFSET(1)] as domain

Upvotes: 4

Nishant Kumar Verma
Nishant Kumar Verma

Reputation: 442

This is sql Query might help somone.

declare @email varchar(50) ='[email protected]';
SELECT SUBSTRING(@email, CHARINDEX('@', @email) + 1, LEN(@email) - CHARINDEX('@', @email) + 1) AS domain_name

Output subdomain.domain.com

DECLARE @var varchar(50) ='[email protected]';
SELECT 
SUBSTRING(@var,0,charindex('@',@var)) as UserName,
Substring(@var,charindex('@',@var)+1,charindex('.',@var,charindex('@',@var,0))-(charindex('@',@var)+1)) as SubDomain,
substring(@var,charindex('.',@var,charindex('@',@var))+1,len(@var)) as DomainName

Output enter image description here

Upvotes: 1

Rohit
Rohit

Reputation: 3157

You could use the REGEXP_EXTRACT function -- 

SELECT REGEXP_EXTRACT('[email protected]', r'@(.+)');

would give 'yahoo.com'.

Upvotes: 15

Related Questions