Can you use a sub-query in a WHERE LIKE clause? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Can you use a sub-query in a WHERE LIKE clause?

Author

Topic

sixside
Starting Member

12 Posts

Posted - 2010-03-27 : 06:13:17

I'm not even sure how to even phrase this as it sounds weird conceptually, but I'll give it a try. Basically I'm looking for a way to create a query that is essentially a WHERE IN LIKE SELECT statement.

As an example, if I wanted to find all user records with a hotmail.com email address, I could do something like:


    SELECT UserEmail 
    FROM Users 
    WHERE (UserEmail LIKE '%hotmail.com')

But what if I wanted to use a subquery as the matching criteria? Something like this:


    SELECT UserEmail 
    FROM Users 
    WHERE (UserEmail LIKE (SELECT '%'+ Domain FROM Domains))

Is that even possible? If so, what's the right syntax?

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-27 : 06:20:53

Try some thing like this.let us know if it helps


SELECT reqd cloumns from tbl1
WHERE emailid in
(SELECT emailid from tbl2 WHERE emailid LIKE '%hotmail.com')

sixside
Starting Member

12 Posts

Posted - 2010-03-27 : 06:36:03

Thanks, but I want the "hotmail.com" part to essentially be dynamic. So it does a wildcard match against records from another table (the table in the subquery).

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-27 : 08:16:00

How about this:


SELECT reqd cloumns 
FROM tbl1       -- Table with the email addresses
INNER JOIN tbl2 -- Table with the domains
	ON tbl1.EmailAddress LIKE '%' + tbl2.Domain

You may have to use a distinct is there is more than one matching row. eg "bob@hotmail.com" will match with "hotmail.com" and "tmail.com", so if both those domains exist, then the "bob@hotmail.com" record will be returned twice. You could also get around this by putting a '@' in the like clause.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources