Tuesday, March 27, 2012

Filter by concatenated field

How can I filter a query using a cocatenated field? For instance, I have a
query (for instance):
Select id,
ClientCity + ' ' + ClientState + ' ' + ClientZip as ClientInfo
from tblClients
I want to be able to say "where ClientInfo like '% ' + @.Parameter + '%'"
so that I can query by "Chicago" or "60001" as parameters
Thanks for your help.The where clause is parsed before the select list, so you can't do that
because the optimizer won't know what you're talking about (or in rare
cases, you will be filtering on the wrong column, e.g. if you actually had a
clientInfo column with different information). A couple of options:
SELECT
id,
ClientInfo = ClientCity + ' ' + ClientState + ' ' + ClientZip
FROM
tblClients
WHERE
ClientCity + ' ' + ClientState + ' ' + ClientZip LIKE '%'+@.Parameter+'%'
or
SELECT
id,
ClientInfo
FROM
(
SELECT id,
ClientInfo = ClientCity + ' ' + ClientState + ' ' + ClientZip
FROM
tblClients
) subquery
WHERE
ClientInfo LIKE '%'+@.Parameter+'%'
"et" <eagletender2001@.yahoo.com> wrote in message
news:e7MI72JEGHA.524@.TK2MSFTNGP09.phx.gbl...
> How can I filter a query using a cocatenated field? For instance, I have
> a query (for instance):
> Select id,
> ClientCity + ' ' + ClientState + ' ' + ClientZip as ClientInfo
> from tblClients
> I want to be able to say "where ClientInfo like '% ' + @.Parameter + '%'"
> so that I can query by "Chicago" or "60001" as parameters
> Thanks for your help.
>
>

No comments:

Post a Comment