用 Replace() 选择 [英] SELECT with a Replace()

问题描述

我有一张姓名和地址表，其中包括一个邮政编码列.我想从邮政编码中去除空格并选择与特定模式匹配的任何空格.我正在 SQL Server 2005 上的 T-SQL 中尝试这个(稍微简化):

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%'

但我收到以下错误；

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

如果我删除 WHERE 子句，我会得到一个没有空格的邮政编码列表，这就是我想要搜索的内容.我应该如何处理这个问题?我做错了什么?

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?

推荐答案

不要直接在 WHERE 子句中使用别名 (P).

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

您可以在 WHERE 子句中再次使用相同的 REPLACE 逻辑:

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.

这篇关于用 Replace() 选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！