如何在SQL中搜索带有可选特殊字符的匹配项？

I have a problem with a search query I am using my data contains names and information that has apostrophes in various forms (HTML encoded and actual). So for example I would like an alternative to this:

我有一个搜索查询的问题我正在使用我的数据包含具有各种形式的撇号(HTML编码和实际)的名称和信息。所以例如我想要一个替代方案:

SELECT * FROM Customers WHERE REPLACE(LastName,'''','') 
LIKE Replace('O''Brien,'''','')

This is just an example, what I want is a way where if someone types OBrien or O'Brien this will still work, I need to replace three versions of the character and the data is feed sourced and cannot be changed - what can be done to a query to allow for this kind of search to work.
I have Items with names which work this way which currently have many nested REPLACE functions and cannot seem to find something that will work this way, which is more efficient.
I am using MS SQL 2000 with ASP if that helps.

这只是一个例子,我想要的是一种方式,如果有人输入OBrien或O'Brien,这仍然可以工作,我需要替换三个版本的角色,数据是源代码,无法更改 - 可以做什么查询以允许此类搜索工作。我的项目名称以这种方式工作,目前有许多嵌套的REPLACE函数,似乎无法找到可以这种方式工作的东西,这样更有效。我正在使用MS SQL 2000与ASP,如果这有帮助。

Edit

Here is the query that needs to match O'Brien or OBrien, this query does this but is too inefficient - it is joined by another for Item Names and FirstName (optional) for matching.

这是需要匹配O'Brien或OBrien的查询,此查询执行此操作但效率太低 - 它由另一个项目名称和FirstName(可选)连接以进行匹配。

SELECT * FROM Customers
WHERE 
REPLACE(REPLACE(REPLACE(LastName,'&apos;',''),'&#39;',''),'''','') 
LIKE 
REPLACE(REPLACE(REPLACE('%O&#39;Brien%','&apos;',''),'&#39;',''),'''','')

4 个解决方案

#1

If you want to stay correct and do this in SQL this is probably the best you can do

如果你想保持正确并在SQL中这样做,这可能是你能做的最好的

SELECT * FROM Customers WHERE 
LastName LIKE 'O%Brien' AND 
REPLACE(LastName,'''','') LIKE 'O''Brien'

You will still get table scans sometimes, due to poor selectivity.

由于选择性差,您有时仍会进行表扫描。

The reason for the first where is to try to use an existing index. The reason for the second match is to ensure that last names like ObbBrien do not match.

第一个原因是尝试使用现有索引。第二场比赛的原因是确保像ObbBrien这样的姓氏不匹配。

Of course the best thing to do would be not to need the ugly replace. This could be achieved in the app by storing an additional clean lastname column. Or in a trigger. Or in an indexed view.

当然,最好的办法是不需要丑陋的替换。这可以通过存储额外的干净姓氏列在应用程序中实现。或者在触发器中。或者在索引视图中。

#2

You could try this:

你可以试试这个:

SELECT * 
FROM Customers 
WHERE LastName LIKE Replace('O''Brien,'''','%')

This should allow it to use an index as you are not modifying the original column.

这应该允许它使用索引,因为您没有修改原始列。

#3

For pure SQL, the escaping is entirely unnecessary.

对于纯SQL,转义完全没必要。

SELECT * FROM Customers WHERE LastName = 'O''Brien'

#4

Use parameters instead of building the queries in code.

使用参数而不是在代码中构建查询。

If you are using ADO you can use a syntax like this:

如果您使用的是ADO,可以使用如下语法:

Dim cmd, rs, connect, intNumber
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = "your connectionstring"
cmd.CommandText = "SELECT * FROM Customers WHERE LastName LIKE @LastName"
cmd.Parameters.Append cmd.CreateParameter("@LastName",,,,"O'Brien")
Set rs = cmd.Execute

This should perform the query and insert the string O'Brien properly formatted for your database.

这应执行查询并插入为数据库正确格式化的字符串O'Brien。

Using parameters ensures that all values are properly formatted and it also protects you against sql injection attacks.

使用参数可确保所有值都已正确格式化,并且还可以保护您免受SQL注入攻击。

#1