SQL 需要一个返回包含指定字母的每个字段的查询

Need a query that returns every field that contains a specified letter

提问by Erik

I have an SQL table with 11000 keywords in it.

我有一个包含 11000 个关键字的 SQL 表。

I want a query that can find fields which contain a certain letter.

我想要一个可以找到包含某个字母的字段的查询。

So, if I include "a" and "b" the query will select all fields which contain the letter "a" and the letter "b" somewhere in the field.

因此，如果我包含“a”和“b”，查询将选择字段中某处包含字母“a”和字母“b”的所有字段。

回答by Tom Ritter

select *
from table
where keyword like '%a%'
and keyword like '%b%'

ps This will be super slow. You may want to investigate full text indexing solutions.

ps 这会超级慢。您可能想研究全文索引解决方案。

回答by KSimons

I'd use wildcard searching.

我会使用通配符搜索。

where <field> like '%[ab]%'

It isn't regex, but it does a good job. You can also do variants like <field> like 'sim[oa]ns'-- which will match simons, and simans...

它不是正则表达式，但它做得很好。你也可以做类似的变体<field> like 'sim[oa]ns'——这将匹配 simons 和 simans ......

Depnding on your collation you may or may not have to include case data, like '%[aAbB]%'

根据您的整理，您可能需要也可能不需要包括案例数据， like '%[aAbB]%'

As mentioned elsewhere be prepared for a wait since indexes are out of the question when you're doing contains searching.

正如其他地方提到的那样，请准备好等待，因为在您进行包含搜索时索引是不可能的。

回答by Charles Bretana

try this

尝试这个

Select * From Table
Where field like '%' + ltrValue1 + '%'
  And field like '%' + ltrValue2 + '%'
... etc.

and be prepared for a table scan as this functionality cannot use any existing indices

并为表扫描做好准备，因为此功能无法使用任何现有索引

回答by Thorsten

All the answers given using LIKEare totally valid, but as all of them noted will be slow. So if you have a lot of queries and not too many changes in the list of keywords, it pays to build a structure that allows for faster querying.

使用给出的所有答案LIKE都是完全有效的，但正如所有人所指出的那样，速度会很慢。因此，如果您有很多查询并且关键字列表没有太多变化，那么构建一个允许更快查询的结构是值得的。

Here are some ideas:

这里有一些想法：

If all you are looking for is the letters a-z and you don't care about uppercase/lowercase, you can add columns containsA .. containsZ and prefill those columns:

如果您要查找的只是字母 az 并且不关心大写/小写，则可以添加列 containsA .. containsZ 并预填充这些列：

UPDATE table
SET containsA = 'X' 
WHERE UPPER(your_field) Like '%A%';

(and so on for all the columns).

（对所有列以此类推）。

Then index the contains.. columns and your query would be

然后索引 contains.. 列，您的查询将是

SELECT 
FROM your_table
WHERE containsA = 'X'
AND containsB = 'X'

This may be normalized in an "index table" iTable with the columns your_table_key, letter, index the letter-column and your query becomes something like

这可以在“索引表”iTable 中标准化，其中包含列your_table_key、字母、索引字母列，并且您的查询变得类似于

SELECT
FROM your_table 
WHERE <key> in (select a.key
    From iTable a join iTable b and a.key = b.key
    Where a.letter = 'a'
    AND b.letter = 'b');

All of these require some preprocessing (maybe in a trigger or so), but the queries should be a lot faster.

所有这些都需要一些预处理（可能在触发器中），但查询应该快得多。

回答by Greg Andora

You can use a cursor and temp table approach so you aren't doing full table scan each time. What this would be doing is populating the temp table with all of your keywords, and then with each string in the @letters XML, it would remove any records from the temp table. At the end, you only have records in your temp table that have each of your desired strings in it.

您可以使用游标和临时表方法，这样您就不必每次都进行全表扫描。这将做的是用您的所有关键字填充临时表，然后使用 中的每个字符串@letters XML，它将从临时表中删除任何记录。最后，您的临时表中只有包含您想要的每个字符串的记录。

declare @letters xml

SET @letters = '<letters>
            <letter>a</letter>
            <letter>b</letter>
        </letters>'

-- SELECTING LETTERS FROM THE XML
SELECT Letters.l.value('.', 'nvarchar(50)') AS letter
FROM @letters.nodes('/letters/letter') AS Letters(l)

-- CREATE A TEMP TABLE WE CAN DELETE FROM IF A RECORD DOESN'T HAVE THE LETTER
CREATE TABLE #TempResults (keywordID int not null, keyWord nvarchar(50) not null)
INSERT INTO #TempResults (keywordID, keyWord)
SELECT employeeID, firstName FROM Employee

-- CREATE A CURSOR, SO WE CAN LOOP THROUGH OUR LETTERS AND REMOVE KEYWORDS THAT DON'T MATCH
DECLARE Cursor_Letters CURSOR READ_ONLY
FOR 
    SELECT Letters.l.value('.', 'nvarchar(50)') AS letter
    FROM @letters.nodes('/letters/letter') AS Letters(l)

DECLARE @letter varchar(50)
OPEN Cursor_Letters

FETCH NEXT FROM Cursor_Letters INTO @letter
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        DELETE FROM #TempResults 
        WHERE  keywordID NOT IN 
        (SELECT keywordID FROM #TempResults WHERE keyWord LIKE '%' + @letter + '%')
    END
    FETCH NEXT FROM Cursor_Letters INTO @letter
END

CLOSE Cursor_Letters
DEALLOCATE Cursor_Letters

SELECT * FROM #TempResults

DROP Table #TempResults
GO

回答by Steve Kass

I'll assume you meant more or less what you said, and you want to find keywords in your table that "contain the letter 'a' and the letter 'b'." Some of the solutions here give the answer to a different question.

我假设您的意思或多或少是您所说的，并且您想在表格中找到“包含字母 'a' 和字母 'b'”的关键字。这里的一些解决方案给出了不同问题的答案。

To get keywords that contain boththe letters 'a' and 'b' in them (as opposed to those that contain eitherletter), you can use 'ab' as the in the query below:

要获取同时包含字母 'a' 和 'b' 的关键字（而不是包含任一字母的关键字），您可以在下面的查询中使用 'ab'：

select
  keyword
from myTable
where not exists (
  select Nums26.i from Nums26
  where Nums26.i <= len(<matchsetstring>) -- or your dialect's equivalent for LEN()
  and keyword not like '%'+substring(<matchsetstring>,Nums26.i,1)+'%' -- adapt SUBSTRING to your dialect
);

The table named "Nums26" should contain a column "i" (indexed for efficiency) that contains each of the values 1 through 26 (or more if you might try to match more than letters). See below. Advice given by others applies with regard to upper/lower case. If your collation is case-sensitive, however, you can't simply specify 'aAbB' here as your , because that would request keywords that contain each of the four characters a, A, b, and B. You might use UPPER and match 'AB', perhaps.

名为“Nums26”的表应包含一列“i”（为效率而编入索引），其中包含 1 到 26 中的每一个值（如果您可能尝试匹配多个字母，则可以使用更多值）。见下文。其他人给出的建议适用于大小写。但是，如果您的排序规则区分大小写，则不能在此处简单地将 'aAbB' 指定为您的 ，因为这将请求包含四个字符 a、A、b 和 B 中的每一个的关键字。您可以使用 UPPER 并匹配'AB'，也许。

create table nums26 (
  i int primary key
);
insert into nums26 values (1);
insert into nums26 select 1+i from nums26;
insert into nums26 select 2+i from nums26;
insert into nums26 select 4+i from nums26;
insert into nums26 select 8+i from nums26;
insert into nums26 select 16+i from nums26;

回答by Crazy Cat

There's most likely a more elegant way, but this does find every record with a letter in it, both upper or lower case:

很可能有一种更优雅的方式，但这确实会发现每个记录中都有一个字母，无论是大写还是小写：

select * from your_table
where UPPER(your_field) like '%A%'
or UPPER(your_field) like '%B%'
or UPPER(your_field) like '%C%'
or UPPER(your_field) like '%D%'
or UPPER(your_field) like '%E%'
or UPPER(your_field) like '%F%'
or UPPER(your_field) like '%G%'
or UPPER(your_field) like '%H%'
or UPPER(your_field) like '%I%'
or UPPER(your_field) like '%J%'
or UPPER(your_field) like '%K%'
or UPPER(your_field) like '%L%'
or UPPER(your_field) like '%M%'
or UPPER(your_field) like '%N%'
or UPPER(your_field) like '%O%'
or UPPER(your_field) like '%P%'
or UPPER(your_field) like '%Q%'
or UPPER(your_field) like '%R%'
or UPPER(your_field) like '%S%'
or UPPER(your_field) like '%T%'
or UPPER(your_field) like '%U%'
or UPPER(your_field) like '%V%'
or UPPER(your_field) like '%W%'
or UPPER(your_field) like '%X%'
or UPPER(your_field) like '%Y%'
or UPPER(your_field) like '%Z%'

回答by Jerry Coffin

select * from your_table where your_field like '%a%b%'

and be prepared to wait a while...

并准备等待一段时间......

Edit: note that this pattern looks for an 'a' followed by a 'b' (possibly with other "stuff" in between) -- rereading your question, that may not be what you wanted...

编辑：请注意，此模式查找 'a' 后跟一个 'b'（可能中间还有其他“东西”）-重新阅读您的问题，这可能不是您想要的...

回答by longneck

where somefield like '%a%' or somefield like '%b%'