使用 Replace() 进行选择

Question

我有一个姓名和地址表，其中包括邮政编码列。我想从邮政编码中删除空格并选择与特定模式匹配的任何邮政编码。我正在 SQL Server 2005 上的 T-SQL 中尝试此操作（稍微简化）：

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

但出现以下错误；

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

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

Answer 1

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

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

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

或者使用 Nick 的答案中所述的别名子查询。

Answer 2

如果你包装查询，你可以这样引用，如下所示：

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

一定要给包装的 select 一个别名，即使没有使用（SQL Server 不允许在没有 IIRC 的情况下使用它）

Answer 3

您正在创建一个别名 P ，稍后在 where 子句中使用相同的别名，这就是造成问题的原因。不要在 where 中使用 P，而是尝试这样做：

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

Answer 4

您必须在任何想要使用它的地方重复您的表达式：

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

或者您可以将其设为子查询

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

Answer 5

为了扩展 Oded 的答案，您的概念模型需要在此处稍作调整。列名别名（SELECT 列表中的 AS 子句）发生在 SELECT 处理的后期，这就是别名不存在的原因可用于 WHERE 子句。事实上，列别名之后发生的唯一事情就是排序，这就是原因（引用 SELECT 上的文档）：

column_alias 可以在 ORDER BY 子句中使用。但是，它不能用在 WHERE、GROUP BY 或 HAVING 子句中。

如果 SELECT 列表中有一个复杂的表达式，当它出现在 SELECT 列表中并且（比如说）一个 时，您可能会担心它“被计算两次” >WHERE 子句 - 然而，查询引擎足够聪明，可以弄清楚发生了什么。如果您想避免表达式在查询中出现两次，您可以执行类似的操作

SELECT c1, c2, c3, expr1
FROM
    ( SELECT c1, c2, c3, some_complicated_expression AS expr1 ) inner
WHERE expr1 = condition

来避免 some_complicated_expression 物理上出现两次。

Answer 6

如果您希望使用索引，请以一致的方式存储数据（删除空格）。只需删除空格或添加持久计算列，然后您只需从该列中进行选择，而不必在每次运行查询时添加所有空格以消除开销。

添加持久计算列：

ALTER TABLE Contacts ADD PostcodeSpaceFree AS Replace(Postcode, ' ', '') PERSISTED 
go
CREATE NONCLUSTERED INDEX IX_Contacts_PostcodeSpaceFree 
ON Contacts (PostcodeSpaceFree) --INCLUDE (covered columns here!!)
go

通过删除空格来修复列使用：

UPDATE Contacts
    SET Postcode=Replace(Postcode, ' ', '')

现在您可以像这样搜索，选择可以使用索引：

--search the PERSISTED computed column
SELECT 
    PostcodeSpaceFree 
    FROM Contacts
    WHERE PostcodeSpaceFree  LIKE 'NW101%'

或

--search the fixed (spaces removed column)
SELECT 
    Postcode
    FROM Contacts
    WHERE PostcodeLIKE 'NW101%'

Answer 7

SELECT *
FROM Contacts
WHERE ContactId IN
    (SELECT a.ContactID
    FROM
        (SELECT ContactId, Replace(Postcode, ' ', '') AS P
        FROM Contacts
        WHERE Postcode LIKE '%N%W%1%0%1%') a
    WHERE a.P LIKE 'NW101%')

Answer 8

这将起作用：

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