Search 'Email address' in where clause in SQL

Payal sathavara
Payal sathavara used Ask the Experts™
on
How to search multiple email address in below SQL query.....

------------------
Declare @EmailId varchar (max)
set @EmailId='anuradha.dutta@xyz.com,HEENA.SANOTRA@xyz.COM'

Declare @String4 varchar (Max)

Set @String4 ='select * from EmailSummary ES
where ES.'+@EmailIdWhere+'
----------------------------------------------------------------------------------
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Show columns from your table EmailSummary ?
What column has email ids?
Payal sathavaraWindows Developer

Author

Commented:
Declare @EmailId varchar (max)
set @EmailId='anuradha.dutta@xyz.com,HEENA.SANOTRA@xyz.COM'

Declare @EmailIdWhere varchar (max)
Set @EmailIdWhere=Case when @EmailId='' then 'EMailAddressesTo<>''''' else 'EMailAddressesTo IN ('''+@EmailId+''')' end

Declare @String4 varchar (Max)

Set @String4 ='select * from EmailSummary ES
where ES.'+@EmailIdWhere+'
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You need to try like below - Split and then join , IN clause will not work here.

--
Declare @EmailId varchar (max) =''
set @EmailId='anuradha.dutta@xyz.com,HEENA.SANOTRA@xyz.COM'

DECLARE @x AS XML=''
SET @x = CAST('<A>'+ REPLACE(@EmailId,',','</A><A>')+ '</A>' AS XML)

SELECT * FROM EmailSummary ES
INNER JOIN 
(
	SELECT t.value('.', 'VARCHAR(200)') Value FROM @x.nodes('/A') AS x(t)
)p ON p.Value = ES.EMailAddressesTo

--

Open in new window

Payal sathavaraWindows Developer

Author

Commented:
Dear Sir,
I have also search with accountID as well as Email then ?

Declare @AccountMasterId varchar (max)
set @AccountMasterId='495,12110'

Declare @AccountMasterIdsWhere varchar (max)
Set @AccountMasterIdsWhere=Case when @AccountMasterId='0' then 'AccountMasterId<>-1111' else 'AccountMasterId in ('+@AccountMasterId+')' end

DECLARE @x AS XML=''
SET @x = CAST('<A>'+ REPLACE(@EmailId,',','</A><A>')+ '</A>' AS XML)

      SELECT * FROM #EmailSummary ES LEFT OUTER JOIN AccountMaster A ON A.EMail = ES.EMailAddressesTo
                  INNER JOIN
                  (
                        SELECT t.value('.', 'VARCHAR(200)') Value FROM @x.nodes('/A') AS x(t)
                  )p ON p.Value = ES.EMailAddressesTo      


I  have join account master and search with AccountMasterID....How to set in Query ?
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Please try like this -

Declare @AccountMasterId varchar (max) = ''
set @AccountMasterId='495,12110'

Declare @EmailId varchar (max) =''
set @EmailId='anuradha.dutta@xyz.com,HEENA.SANOTRA@xyz.COM'

DECLARE @x AS XML=''
SET @x = CAST('<A>'+ REPLACE(@EmailId,',','</A><A>')+ '</A>' AS XML)

DECLARE @y AS XML=''
SET @y = CAST('<A>'+ REPLACE(@AccountMasterId,',','</A><A>')+ '</A>' AS XML)

SELECT u.* FROM 
(
	SELECT * FROM #EmailSummary ES 
	INNER JOIN 
	(
		SELECT t.value('.', 'VARCHAR(200)') Value FROM @x.nodes('/A') AS x(t)
	)p ON p.Value = ES.EMailAddressesTo  
)u
LEFT OUTER JOIN 
(
	SELECT  * FROM AccountMaster A
	INNER JOIN 
	(
		SELECT t.value('.', 'VARCHAR(200)') Value FROM @y.nodes('/A') AS x(t)
	)o 
)A ON A.EMail = u.EMailAddressesTo

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial