query with like

How do I build a query using like, but the options come from a table? Such
as
Select lname from tblClients where lname like '%smith%'
but the "smith" comes from another table, such as
select lname from lnamelist.Simple example using the Pubs database.
declare @.lname varchar(50)
select @.lname = au_lname from authors where au_id = '172-32-1176'
Select * from authors
where au_lname like '%' + @.lname + '%'
HTH
Jerry
"et" <eagletender2001@.yahoo.com> wrote in message
news:%23TdPK6TvFHA.464@.TK2MSFTNGP15.phx.gbl...
> How do I build a query using like, but the options come from a table?
> Such as
> Select lname from tblClients where lname like '%smith%'
> but the "smith" comes from another table, such as
> select lname from lnamelist.
>|||Something like:
SELECT *
FROM Clients c1
WHERE EXISTS ( SELECT *
FROM Names n1
WHERE c1.last_name LIKE '%' + n1.last_name + '%' );
Anith|||Example:
select a.*, b.*
from t1 as a inner join t2 as b on a.c1 like '%' + b.c1 + '%'
Do not expect that sql server performs an index s if there is an index in
[t1] by [c1], because this kind of expressions (using "%" wildcard at the
begining) are not considered as search arguments.
AMB
"et" wrote:

> How do I build a query using like, but the options come from a table? Suc
h
> as
> Select lname from tblClients where lname like '%smith%'
> but the "smith" comes from another table, such as
> select lname from lnamelist.
>
>|||You can join the two:
SELECT C.lane
FROM Clients AS C
JOIN Names AS N
ON C.lname LIKE '%' + N.lname + '%';
BG, SQL Server MVP
www.SolidQualityLearning.com
"et" <eagletender2001@.yahoo.com> wrote in message
news:%23TdPK6TvFHA.464@.TK2MSFTNGP15.phx.gbl...
> How do I build a query using like, but the options come from a table?
> Such as
> Select lname from tblClients where lname like '%smith%'
> but the "smith" comes from another table, such as
> select lname from lnamelist.
>|||et wrote:
> How do I build a query using like, but the options come from a table?
> Such as
> Select lname from tblClients where lname like '%smith%'
> but the "smith" comes from another table, such as
> select lname from lnamelist.
If the entire string '%smith%' is stored in another table, you could use
something like:
Select
lname
From
dbo.tblClients
Where
lname LIKE (Select lname from dbo.lname_table where lnameid =
@.some_pk_value)
But something tells me your table does not have the percent signs in the
string. If that's the case, you need to use dynamic sql, which has some
drawbacks:
http://www.sommarskog.se/dynamic_sql.html
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David Gugick wrote:
> forget what I wrote
My mind is mush today...
David Gugick|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:e2bjUAUvFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Something like:
> SELECT *
> FROM Clients c1
> WHERE EXISTS ( SELECT *
> FROM Names n1
> WHERE c1.last_name LIKE '%' + n1.last_name + '%' );
> --
> Anith
>
I haven't run this yet, but wouldn't this create a correlated subquery where
the SELECT in the exists clause gets run once for every c1.last_name in the
outer query?
Simply wondering if my head is working properly today.
Rick Sawtell
MCT, MCSD, MCDBA|||Yes, it does create a correlation. But functionally, it is the same as the
ones posted by Alejandro or Itzik.
Anith