Reuse calculated column in WHERE clause

Question

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

Simplified example:

Working, but with repeated calculation:

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

There is no way to reuse the calculated field on the same level SELECT. You will need to nest it in order to use the 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 clauses are executed in a SQL query. As you can see in the way the clauses are listed, the SELECT clause, where the alias is generated, is executed after the WHERE clause.

Thus, the alias is not "visible" in the WHERE clause, because the alias is generated after the WHERE is applied.

Answer 2

Another variation of 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 abuse 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%';