SQL Use alias in Where statement

You can use "having" instead of "where".

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

Having do a "where" after execution of the query. Be careful to use it in the right conditions to have no performance problem.

Use a subquery:

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

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