如何在需要使用通配符的列中搜索多个值? [英] How do I search for multiple values in a column where I need to use wildcards?

问题描述

在SQL Server中，我需要在列中搜索多个值，但是我没有确切的值，因此也需要使用通配符.

In SQL Server, I need to search a column for multiple values, but I don't have the exact values, so I need to use wildcards as well.

我当前的查询如下:

SELECT * 
FROM table 
WHERE fieldname in ( '%abc1234%',
                     '%cde456%',
                     '%efg8976%')

这不会返回任何结果，但是如果我搜索任何一个单独的值，我都会找到它，所以我知道它们在那里.缺少多个OR(缺少数百个值)有点麻烦，有没有办法做到这一点?

This doesn't return any results, and yet if I search for any one individual value, I find it, so I know they're in there. Short of doing multiple OR's, which is a bit unwieldy with several hundred values, is there a way to do this?

我也想知道为什么这个查询不起作用，因为没有％的同一个查询就可以正常工作(除了只捕获几个完全匹配的小问题之外).

I'd also be interested to know why this query doesn't work, since the same query without the %'s works just fine (except for the small problem of only catching the few exact matches).

推荐答案

使用 :

Look at using a Fulltext Index. That should do a much better job with your search, and make your "OR" problem a little nicer to boot:

SELECT * 
FROM table 
WHERE CONTAINS(fieldname, '"abc1234" OR "cde456" OR "efg8976"')

另请参阅:

http://www.simple-talk.com/sql/learn-sql-server/full-text-indexing-workbench/

这篇关于如何在需要使用通配符的列中搜索多个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！