SQL 用 Replace() 选择

SELECT with a Replace()

提问by Andy Johnson

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:

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

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?

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

回答by Oded

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

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

You can either use the same REPLACElogic again in the WHEREclause:

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

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

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

或者使用尼克的答案中描述的别名子查询。

回答by Nick Craver

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)

一定要给包装好的选择一个别名，即使是未使用的（SQL Server 不允许它没有一个 IIRC）

回答by KM.

if you want any hope of ever using an index, store the data in a consistent manner (with the spaces removed). Either just remove the spaces or add a persisted computed column, Then you can just select from that column and not have to add all the space removing overhead every time you run your query.

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

add a PERSISTED computed column:

添加一个 PERSISTED 计算列：

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

to just fix the column by removing the spaces use:

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

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

now you can search like this, either select can use an index:

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

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

or

或者

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

回答by Sarfraz

You are creating an alias Pand later in the whereclause you are using the same, that is what is creating the problem. Don't use Pin where, try this instead:

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

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

回答by Damien_The_Unbeliever

You have to repeat your expression everywhere you want to use it:

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

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

or you can make it a subquery

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

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

回答by AakashM

To expand on Oded's answer, your conceptual model needs a slight adjustment here. Aliasing of column names (ASclauses in the SELECTlist) happens very late in the processing of a SELECT, which is why alias names are not available to WHEREclauses. In fact, the only thing that happens after column aliasing is sorting, which is why (to quote the docs on SELECT):

为了扩展Oded 的回答，您的概念模型需要在这里稍作调整。列名（列表中的AS子句SELECT）的SELECT别名在 a 的处理过程中发生得非常晚，这就是WHERE子句无法使用别名的原因。事实上，列别名后唯一发生的事情就是排序，这就是为什么（引用 上的文档SELECT）：

column_aliascan be used in an ORDER BY clause. However, it cannot be used in a WHERE, GROUP BY, or HAVINGclause.

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

If you have a convoluted expression in the SELECTlist, you may be worried about it 'being evaluated twice' when it appears in the SELECTlist and (say) a WHEREclause - however, the query engine is clever enough to work out what's going on. If you want to avoid having the expression appear twice in your query, you can do something like

如果SELECT列表中有一个令人费解的表达式，当它出现在SELECT列表和（比如）WHERE子句中时，您可能会担心它会“被评估两次” ——但是，查询引擎足够聪明，可以弄清楚发生了什么。如果您想避免在查询中出现两次表达式，您可以执行以下操作

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

which avoids some_complicated_expressionphysically appearing twice.

这避免了some_complicated_expression物理上出现两次。

回答by Anthony Faull

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

回答by Ibama

This will work:

这将起作用：

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