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

Answer 1

I think it is not possible, but maybe you can take a look on Common Table Expressions over SQL 2005

Like this:

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

Answer 2

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

(I know this works in Oracle, I believe it is standard SQL and would work in MSSQL.)

Answer 3

Use a subquery:

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

Answer 4

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

Answer 5

use a view or a derived table.

Using a derived table, your example would look like:

select col1 
from 
(SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3)  AS Col1
FROM MyTable) 
where col1='Mysearch'

Answer 6

The answer is you can't - you can do this

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

Answer 7

Actually, using alias won't make your query any faster as SQL optimizer is not as dumb as you think, so I'd just repeat the SUBSTRING expression again.

Answer 8

You can do this:

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