SQL Use alias in Where statement

Question

I wounder how I could use an alias in a where statement.

Example :

SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3)  AS Col1
FROM MyTable
WHERE Col1 = 'MySearch'

I use MSSQL 2005

question from:https://stackoverflow.com/questions/715462/sql-use-alias-in-where-statement

Answer 1

Not possible, but you can do the following:

SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3)  AS Col1
FROM MyTable
WHERE SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) = 'MySearch'

No subqueries or hacks required