Reusing a computed column in a WHERE clause

Question

In the SELECT statement, I want to reuse the calculated column in the WHERE clause. Is there a way to do this in SQLserver?

A simplified example:

Work, but with recalculation:

SELECT field1, concat(field2, field3) AS calc_field FROM MyTable WHERE concat(field2, field3) LIKE 'A%' 

Desired:

 SELECT field1, concat(field2, field3) AS calc_field FROM MyTable WHERE calc_field LIKE 'A%' 

Answer 1

Cannot reuse computed field at same SELECT level. You will need to nest it to use an alias.

 SELECT field1 , calc_field FROM ( SELECT field1 , CONCAT (field2, field3) AS calc_field FROM MyTable ) tbl WHERE calc_field LIKE 'A%' 

This is because of the order in which the statements are executed in the SQL query . As you can see how the sentences are listed, the SELECT where the alias is generated is executed after the WHERE .

Thus, the alias is not “visible” in the WHERE because the alias is generated after applying WHERE .

Answer 2

Another option for existing answers is to use a common table expression (CTE):

 WITH cte AS ( SELECT field1, concat(field2, field3) AS calc_field FROM mytable ) SELECT field1, calc_field FROM cte WHERE calc_field LIKE 'A%' 

You can also use CROSS APPLY :

 SELECT field1, ca.calc_field FROM MyTable CROSS APPLY ( SELECT concat(field2, field3) AS calc_field ) AS ca WHERE ca.calc_field LIKE 'A%'; 

Answer 3

Wrap it in a view:

 select field1, calc_field from ( SELECT field1, concat(field2, field3) AS calc_field FROM MyTable ) dt WHERE calc_field LIKE 'A%' 

Answer 4

Using sub Query to achieve this result:

 select field1, calc_field from ( SELECT field1, concat(field2, field3) AS calc_field FROM MyTable ) as dt WHERE calc_field LIKE 'A%' 

Answer 5

For SQL Server, you can use + as a concatenation identifier

 SELECT field1, field2 +field3 AS calc_field FROM MyTable WHERE field2 + field3 LIKE 'A%' 

For best performance, avoid using concatenated fields in the WHERE clause. Depending on your requirements, WHERE (field2 LIKE 'A%' OR field3 LIKE 'A%') might be a better option