SELECT with a Replace()

Question

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 FROM Contacts WHERE P LIKE 'NW101%' 

But I get the following error;

Msg 207, Level 16, State 1, Line 3 Invalid column name 'P'. 

If I remove the WHERE clause I get a list of postcodes without spaces, which is what I want to search. How should I approach this? What am I doing wrong?

Answer 1

Don't use the alias (P) in your WHERE clause directly.

You can either use the same REPLACE logic again in the WHERE clause:

SELECT Replace(Postcode, ' ', '') AS P FROM Contacts WHERE Replace(Postcode, ' ', '') LIKE 'NW101%' 

Or use an aliased sub query as described in Nick's answers.

Answer 2

You can reference is that way if you wrap the query, like this:

SELECT P FROM (SELECT Replace(Postcode, ' ', '') AS P       FROM Contacts) innertable WHERE P LIKE 'NW101%' 

Be sure to give the wrapped select an alias, even unused (SQL Server doesn't allow it without one IIRC)