Question

SQL Use alias in Where statement

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

 45  98296  45
1 Jan 1970

Solution

 75

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.

2013-06-26

Solution

 24

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

2009-04-03