T-SQL: Can you reference a SELECT column expression in the WHERE clause without repeating the entire expression?

The following won't work, right?
select FName+LName as FullName
from Employees
where FullName='Montgomery Scott'
Is there any way to reference the result of the column expression (FName+LName) using relative ordering, like this?
select FName+LName
from Employees
where [Column 0]='Montgomery Scott'
Or are you stuck repeating the entire expression like this?
select FName+LName
from Employees
where FName+LName='Montgomery Scott'
Does someone have a trick or a workaround?

I know this works but I'm trying to avoid a needless nested/derived table:
select * from (select FName+LName as FullName
from Employees) MyDerivedTable
where FullName='Montgomery Scott'
LVL 2
ZuZuPetalsAsked:
Who is Participating?
 
DFW_EdConnect With a Mentor Commented:
here's the oder of operations:

http://www.bennadel.com/blog/70-SQL-Query-Order-of-Operations.htm

because the compiler hasn't gotten to the "select" list before reading the "where" clause you're not able to use derived columns like you can in an "order by"
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>Does someone have a trick or a workaround?
sorry, but you have found all the details about this "issue"
you have to repeat the expression, or use a derived table

unless you consider to create, in the table, a computed column "fullname" with that expression ...

0
 
Andre412Commented:
Im guessing it would be too obvious split the where

select FName+LName as FullName
from Employees
where FullName='Montgomery Scott'

becomes

select FName+LName as FullName
from Employees
where FName= 'Montgomery' and LName = 'Scott'

your only alternative is select from the select

Select fullname From
       (select firstname + ' ' + surname [fullname]
      from [user]) U
      where U.[fullname] like '%fred%'
0
 
Alpesh PatelAssistant ConsultantCommented:
No, You cannot directly use the object in where condition. you have to put full expression.
0
 
ZuZuPetalsAuthor Commented:
The order of operations really helped me--thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.