Using the field name alias in SQL Where clause

Written by Deepak Vasa on March 17th, 2009

Often I encounter SQL queries where I would love to use the alias names of my columns in the WHERE clause, today while trawling around the web I found a cool and easy way to do it. Here’s an example:

WITH Customer_Info AS
(
SELECT (Surname + ‘, ‘ + Firstname) AS FullName,
(Address_Line_1 + ‘,’ + Address_Line_2) AS Customer_Address
FROM Customers
)
SELECT FullName, Customer_Address
FROM Customer_Info
WHERE FullName LIKE ‘Smith%’

Written by Deepak Vasa - Visit Website
 

3 Comments so far ↓

  1. Kevin Kelly says:

    This is a very cool way to alias a table definition so that the derived columns can be used in the where clause. This helped me greatly in solving an issue today. Thanks for posting this tip.

  2. Greg Dietrich says:

    WOW. Wish I had found this months ago.

  3. Tania Gleave says:

    J’aime vraiment sont . Ce type de rapport! Continuez votre superbe gars travaille j’ai incorporé ajoutées !..

Leave a Comment