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.
>
>

Filter by a concatenated column?

I am using a query in a stored procedure, where the user can dictate which
field they want to sort by. That I can do. What I also want to do is
filter out the null values for the selected field, and one of the fields is
a concatenated result. This is the query:
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
so the stored procedure would be something like this:
CREATE PROCEDURE [dbo].ClientSearch]
@.SearchField as varchar(50)='' AS
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
where @.SearchField is not null order by @.SearchField
If the user is selecting FullName, I get an error saying the @.SearchField is
an invalid column name -- however this works if other field names are
selected, and it also works if I only want to order by FullName, but not
filter.
Thanks for your help.I hope you are using exec since its a dyamic query.
fullname is a concatenationo of two fields LName + ', ' + FName
therefore LName + ', ' + FName is not null does not work.
make sure that LName is not null and FNAME is not NULL
HTH
Rajesh Peddireddy.
"news.microsoft.com" wrote:

> I am using a query in a stored procedure, where the user can dictate which
> field they want to sort by. That I can do. What I also want to do is
> filter out the null values for the selected field, and one of the fields i
s
> a concatenated result. This is the query:
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> so the stored procedure would be something like this:
> CREATE PROCEDURE [dbo].ClientSearch]
> @.SearchField as varchar(50)='' AS
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> where @.SearchField is not null order by @.SearchField
> If the user is selecting FullName, I get an error saying the @.SearchField
is
> an invalid column name -- however this works if other field names are
> selected, and it also works if I only want to order by FullName, but not
> filter.
> Thanks for your help.
>
>