Using the field name alias in SQL Where clause
Written by Deepak Vasa on March 17th, 2009Often 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%’
12
AM
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.
23
AM
WOW. Wish I had found this months ago.