SQL 数据库中的搜索列忽略特殊字符 [英] Search column in SQL database ignoring special characters

问题描述

有人知道是否可以对 SQL Server 数据库中的列进行 %LIKE% 搜索，但让它忽略列中的任何特殊字符?

Does anybody know if it's possible to do a %LIKE% search against a column in a SQL Server database but get it to ignore any special characters in the column?

因此，例如，如果我有一个名为歌曲"的列并且它们包含以下内容...

So, for example if I have a column called "songs" and they contain the following...

黑色或白色

直到布鲁克林不眠

船歌

爸爸不要说教

如果用户搜索no sleey until brooklyn"，那么即使他们忘记包含撇号，我也希望它返回匹配项.如果他们搜索SOUL"，我也希望它返回第 4 行.我相信你明白了......

If the user searches for "no sleey till brooklyn" then I would like it to return a match even though they forgot to include the apostrophe. I would also like it to return the 4th row if they search for "SOUL". I'm sure you get the idea....

任何帮助将不胜感激.

推荐答案

我会考虑使用全文索引，然后您可以使用 FREETEXT 和 CONTAINS 的强大功能进行搜索.

I would look into using a Full Text Index and then you can use the power of FREETEXT and CONTAINS to do your search.

我仍然会考虑改进全文索引搜索，但是，从另一个答案开始，这是使用 REPLACE 的一个选项.

I would still look into refining the Full Text Index searching, however, to follow on from another answer, this is an option using REPLACE.

SELECT
    Artist,
    Title
FROM
    Songs
WHERE
    REPLACE(REPLACE(REPLACE(Artist, '#',''), '*', ''), '"', '') LIKE '%Keywords%'

这篇关于SQL 数据库中的搜索列忽略特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！