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

发布于 2024-07-19 09:05:48 字数 749 浏览 10 评论 0原文

我的搜索查询有问题，我使用的数据包含带有各种形式的撇号的名称和信息（HTML 编码的和实际的）。例如，我想要一个替代方案：

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

这只是一个例子，我想要的是一种方法，如果有人输入 OBrien 或 O'Brien 这仍然有效，我需要替换角色的三个版本，数据是feed 来源且无法更改 - 可以对查询执行哪些操作才能允许此类搜索工作。
我有一些名称以这种方式工作的项目，目前有许多嵌套的 REPLACE 函数，并且似乎找不到以这种方式工作的东西，这更有效。
如果有帮助的话，我正在将 MS SQL 2000 与 ASP 结合使用。

编辑

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

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

原文

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:

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.

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.

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情何以堪。 2024-07-26 09:05:48

如果您想保持正确并在 SQL 中执行此操作，这可能是您能做的最好的选择。

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

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

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

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

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

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.

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.

回复收藏 0 原文

╰つ倒转 2024-07-26 09:05:48

您可以尝试这样做：

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

这应该允许它使用索引，因为您不修改原始列。

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.

回复收藏 0 原文

小糖芽 2024-07-26 09:05:48

对于纯 SQL，转义是完全没有必要的。

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

For pure SQL, the escaping is entirely unnecessary.

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

回复收藏 0 原文

过气美图社 2024-07-26 09:05:48

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

如果您使用的是 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

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

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

Use parameters instead of building the queries in code.

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

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.

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

回复收藏 0 原文

~没有更多了~