Solved

what's wrong with this ? - sum(if contains "@", [Email]) then 1 else 0 end

Posted on 2014-10-27
5
113 Views
Last Modified: 2015-01-07
I need to do a calculated field that basically tells me how many emails exist in a field called email.
I have 36k records and want to do a quick split between how many have emails and how many don't

I've used this but it fails

sum(if contains "@", [Email]) then 1 else 0 end
0
Comment
Question by:Chris Michalczuk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40406752
Copy-paste the below code into your SSMS, execute it to verify it does what you need, and then modify it to fit your needs.  Uses LIKE and underscores to validate email addresses.
IF OBJECT_ID('tempdb..#email') IS NOT NULL
   DROP TABLE #email
GO

CREATE TABLE #email (addr nvarchar(50)) 

INSERT INTO #email (addr) 
VALUES 
   ('a@b.c'), 
   ('goo@foo.boo'), 
   ('@dabba.doo'), 
   ('yada@yada'),
   ('jim@jimbo.biz'),
   ('not an email address') 

-- Return the whole set
SELECT * FROM #email

-- Underscore operator - Means any single character, so @dabba.doo and yada@yada are not returned. 
-- Correctly formatted email addresses:
SELECT * 
FROM #email
WHERE addr LIKE '_%@_%._%'

Open in new window

0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40406756
Try

sum(iif(EmailAddress like '%@%', 1,0))
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40406764
select case when [email] like '%@%' then 1 else 0 end from mytable

Open in new window


or

select count(email) from mytable where email like '%@%'

Open in new window

0
 

Author Comment

by:Chris Michalczuk
ID: 40407792
my app (Tableau) returns this
expect closing parenthesis or comma while parsing arguement list for iif

for
sum(iif(EmailAddress like '%@%', 1,0))
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40535734
Was this a Tableau question?
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question