I have a table of names and addresses, which includes a postcode column. I want to strip the spaces from the postcodes and select any that match a particular pattern. I'm trying this (simplified a bit) in T-SQL on SQL Server 2005:
SELECT Replace(Postcode, ' ', '') AS P
WHERE P LIKE 'NW101%'
Msg 207, Level 16, State 1, Line 3
Invalid column name 'P'.
Don't use the alias (
P) in your
WHERE clause directly.
You can either use the same
REPLACE logic again in the
SELECT Replace(Postcode, ' ', '') AS P FROM Contacts WHERE Replace(Postcode, ' ', '') LIKE 'NW101%'
Or use an aliased sub query as described in Nick's answers.