WHERE on AS field - 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

SQL Server 2005 Forums

Transact-SQL (2005)

WHERE on AS field

Author

Topic

gsauns
Starting Member

4 Posts

Posted - 2007-11-06 : 16:46:41

I have a query where I am concatenating two fields into one. I have a web page where it is possible to search my database. I would like to be able to search this concatenated field, but it doesn't fly. This is what I hope to accomplish:

SELECT LNAME + ', ' + FNAME AS Name
FROM Table
WHERE Name LIKE '%Criteria%'

but SQL Server (2005) doesn't like the column name 'Name'.

How can I accomplish this?

dbaerwald
Starting Member

5 Posts

Posted - 2007-11-06 : 17:04:52

Well a possible solution...

Try this instead of using the alias

SELECT Last_name + ', ' + First_Name
FROM TABLE
WHERE Last_name + ', ' + First_Name LIKE '%CRITERIA%'

Its not as useful as being able to use an alias, but if its a small query it should work ok.

Good luck,

David

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 17:19:19

When concatenating FNAME and LNAMN they can't be LIKE CRITERIA...

SELECT LNAME + ', ' + FNAME AS Name
FROM Table
WHERE LName LIKE '%Criteria%' or FNAME LIKE '%Criteria%'

E 12°55'05.25"
N 56°04'39.16"

gsauns
Starting Member

4 Posts

Posted - 2007-11-06 : 17:19:53

Well, this query is bound to an ASP.NET GridView, so that workaround isn't going to cut it. Thanks for trying though... any other takers?

gsauns
Starting Member

4 Posts

Posted - 2007-11-06 : 17:21:20

Hmmm... I was hoping to have them be able to search for both using a comma, such as "son, a" would return "johnson, aaron" or something along those lines.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 17:31:44

Use a derived table, if you are not going for dbaerwald's suggestion.

select name from (
SELECT LNAME + ', ' + FNAME AS Name
FROM Table) as d
WHERE Name LIKE '%Criteria%'

E 12°55'05.25"
N 56°04'39.16"

gsauns
Starting Member

4 Posts

Posted - 2007-11-06 : 19:01:33

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources