Like condition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Like condition

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2014-10-30 : 10:56:03

Hi All,

I am trying to retrieving all email address using particular domain extension for e.g. gmail.com

I wrote the below query

Select policyid,emailaddress from policyholder
WHERE policyholder.EmailAddress like '%gmail.com%'

It works fine, but i want to use the LIKE condition against ‘emailaddress’ column in email table as I have more than 100 domain extensions

I wrote something like
WHERE policyholder.EmailAddress like (select emailaddress from email) but I am getting the below error

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How to modify the query, any help will be appreciated.

Regards,
SG

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-30 : 11:30:57

Not clear to me what the organization of the data is - whether email address in email table is a subset of emailaddress in policyholder table, or vice versa, or something else. In any case, perhaps this?

SELECT  policyid ,
        emailaddress
FROM    policyholder p
		INNER JOIN Email e ON e.emailaddress = p.EmailAddress
WHERE   e.emailaddress LIKE '%gmail.com'

satheesh
Posting Yak Master

152 Posts

Posted - 2014-10-30 : 12:05:04

Hi James,

Thanks for your reply. I think i am not clear in my writing. In 'emailaddress' column in email table, i just have
domain extensions for eg. hotmail.com,yahoo.com,123yyy.com etc and i am trying to retrieve all policyholder having email in those domain

Output
123@hotmail.com
123@yahoo.com
123@123yyy.com
--
--

Thanks

Regards,
SG

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-30 : 12:11:32

Assuming the entries in the entries in the email table are unique

SELECT  p.policyid ,
        p.emailaddress
FROM    policyholder p
		INNER JOIN Email e ON p.EmailAddress LIKE '%' + e.emailaddress -- + '%';

satheesh
Posting Yak Master

152 Posts

Posted - 2014-10-31 : 12:06:17

Thanks James

Working Perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources