I'm trying to compare the columns of a table and fetch the unmatched rows.Can someone help me to write a query to fetch the rows where Email_ID not in the format of Firstname.Lastname@abc.com in the below scenario. TABLE S.no Firstname Lastname Email_ID 701 Sean Paul Sean.Paul@abc.com 702 Mike Tyson Mike.Samuel@abc.com 703 Richard Bernard Jim.Anderson@abc.com 704 Simon Sharma Rita.sharma@abc.com

Reputation: 91

Compare columns of same table in sql server

I'm trying to compare the columns of a table and fetch the unmatched rows.Can someone help me to write a query to fetch the rows where Email_ID not in the format of [email protected] in the below scenario.

TABLE

S.no  Firstname	 Lastname	  Email_ID
701	   Sean	          Paul	       [email protected]
702	   Mike	          Tyson	       [email protected]
703	  Richard	  Bernard      [email protected]
704	   Simon	  Sharma       [email protected]

Upvotes: 1

Answers (3)

Evaldas Buinauskas

Reputation: 14077

SELECT *
FROM YourTable
WHERE CHARINDEX(FirstName + '.' + LastName + '@', Email) = 0

Upvotes: 1

jarlh

Reputation: 44696

Search for rows where mail address not like Concat( firstname . lastname @)

select * from tablename
where Email_ID NOT LIKE (Firstname + '.' + Lastname + '@%')

Upvotes: 2

Richard

Reputation: 108975

You mean something like:

select -- some columns
from table
where Email_ID <> (FirstName + '.' + Lastname + '@abc.com')

There is nothing in SQL to prevent comparing one column with another, and – with a little more syntax – even across rows.

Upvotes: 3

Compare columns of same table in sql server

Answers (3)

Related Questions