我已经在几个地方读过，MS SQL中不支持前导通配符搜索（例如使用* overflow来匹配stackoverflow）。我正在考虑使用 CLR功能添加正则表达式匹配，但我很好奇看看其他人可能会有哪些解决方案。
更多信息：只能在单词或短语的末尾加上星号。 / a> - 以及我的实证经验：当匹配myvalue，my *有效时，但（星号）值返回不匹配时，执行查询时简单如下： SELECT * FROM TABLENAME WH CONTAINS（TextColumn，'* searchterm'）;因此，我需要一种解决方法。我只是在实际的搜索页面上在我的网站中使用搜索 - 所以它的工作方式与Google的工作方式基本相同（在Joe Sixpack类型的用户眼中）。并非如此复杂，但这种匹配确实不应该失败。
解决方案解决方案
解决方法仅适用于前导通配符：
- 在另一个字段（或物化视图）中存储颠倒的文本
- 创建全文索引本栏
-
使用*
查找反转文本b $ b $ p $ SELECT * 从TABLENAME 在哪里（TextColumnREV，'mrethcraes *'）;
当然有很多缺点，快速解决方法...
更不用说CONTAINSTABLE ...
Note: I am using SQL's Full-text search capabilities, CONTAINS clauses and all - the * is the wildcard in full-text, % is for LIKE clauses only.
I've read in several places now that "leading wildcard" searches (e.g. using "*overflow" to match "stackoverflow") is not supported in MS SQL. I'm considering using a CLR function to add regex matching, but I'm curious to see what other solutions people might have.
More Info: You can add the asterisk only at the end of the word or phrase. - along with my empirical experience: When matching "myvalue", "my*" works, but "(asterisk)value" returns no match, when doing a query as simple as:
SELECT * FROM TABLENAME WHERE CONTAINS(TextColumn, '"*searchterm"');Thus, my need for a workaround. I'm only using search in my site on an actual search page - so it needs to work basically the same way that Google works (in the eyes on a Joe Sixpack-type user). Not nearly as complicated, but this sort of match really shouldn't fail.
解决方案Workaround only for leading wildcard:
- store the text reversed in a different field (or in materialised view)
- create a full text index on this column
find the reversed text with an *
SELECT * FROM TABLENAME WHERE CONTAINS(TextColumnREV, '"mrethcraes*"');
Of course there are many drawbacks, just for quick workaround...
Not to mention CONTAINSTABLE...
更多推荐
如何让领先的通配符全文搜索在SQL Server中工作？
发布评论