blocks|key|3943246|text|select+*
from+table
where+keyword+like+'%25a%25'
and+keyword+like+'%25b%25'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3943247|ps这将是非常慢的。您可能希望研究全文索引解决方案。|unstyled|3943248|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select *
from table
where keyword like '%a%'
and keyword like '%b%'
</code></pre>

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

blocks|key|1833627|text|我会使用通配符搜索。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1833628|where+<field>+like+'%25[ab]%25'|code-block|syntax|javascript|1833629|它不是正则表达式，但它做得很好。您还可以做一些变体，如<field>+like+'sim[oa]ns'+--它将匹配simons和simans……|offset|length|style|CODE|1833630|根据您的排序规则，您可能必须包含案例数据，也可能不包含，like+'%25[aAbB]%25'|1833631|正如在其他地方提到的，准备好等待，因为当您执行包含搜索时，索引是不可能的。|1833632|entityMap^0|0|0|R|O|0|S|F|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@$I|Y|J|Z|K|L]]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

I'd use wildcard searching.

<pre><code>where &lt;field&gt; like '%[ab]%'
</code></pre>

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

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

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

blocks|key|1833564|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1833565|Select+*+From+Table
Where+field+like+'%25'+%2B+ltrValue1+%2B+'%25'
++And+field+like+'%25'+%2B+ltrValue2+%2B+'%25'
...+etc.|code-block|syntax|javascript|1833566|并为表扫描做好准备，因为此功能不能使用任何现有索引|1833567|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

try this

<pre><code>Select * From Table
Where field like '%' + ltrValue1 + '%'
 And field like '%' + ltrValue2 + '%'
... etc.
</code></pre>

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

blocks|key|5360865|text|使用LIKE给出的所有答案都是完全有效的，但正如所有这些答案所指出的那样，它们将是缓慢的。因此，如果您有很多查询，但关键字列表中没有太多更改，那么构建一个允许更快查询的结构是值得的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5360866|以下是一些想法：|5360867|如果您只查找字母a-z，并且不关心大小写，则可以添加列containsA+..containsZ并预先填充这些列：|5360868|UPDATE+table
SET+containsA+=+'X'+
WHERE+UPPER(your_field)+Like+'%25A%25';|code-block|syntax|javascript|5360869|(对于所有列，依此类推)。|5360870|然后索引contains。列，您的查询将是|5360871|SELECT+
FROM+your_table
WHERE+containsA+=+'X'
AND+containsB+=+'X'|5360872|这可以在“索引表”列中规范化，其中列有your_table_key、letter、iTable和letter-column，您的查询如下所示|5360873|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');|5360874|所有这些都需要一些预处理(可能是在触发器中)，但查询速度应该快得多。|5360875|entityMap^0|2|4|0|0|0|0|0|0|0|J|E|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|15|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|16|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|17|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|18|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|19|8|@]|D|@]|E|$]]|$1|S|3|T|5|L|7|1A|8|@]|D|@]|E|$M|N]]|$1|U|3|V|5|6|7|1B|8|@$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|W|3|X|5|L|7|1E|8|@]|D|@]|E|$M|N]]|$1|Y|3|Z|5|6|7|1F|8|@]|D|@]|E|$]]|$1|10|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]]|11|$]]

All the answers given using <code>LIKE</code>are 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.

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:

<pre><code>UPDATE table
SET containsA = 'X' 
WHERE UPPER(your_field) Like '%A%';
</code></pre>

(and so on for all the columns).

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

<pre><code>SELECT 
FROM your_table
WHERE containsA = 'X'
AND containsB = 'X'
</code></pre>

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

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

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

blocks|key|5360791|text|where+somefield+like+'%25a%25'+or+somefield+like+'%25b%25'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|5360792|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>where somefield like '%a%' or somefield like '%b%'
</code></pre>

blocks|key|1833540|text|select+*+from+your_table+where+your_field+like+'%25a%25b%25'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1833541|准备好等待一段时间...|unstyled|1833542|编辑:请注意，此模式查找'a‘后跟'b’(可能中间还有其他‘东西’)+--重读你的问题，这可能不是你想要的……|1833543|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>select * from your_table where your_field like '%a%b%'
</code></pre>

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...

blocks|key|1833611|text|您可以使用游标和临时表方法，这样就不必每次都进行全表扫描。这样做的目的是用您的所有关键字填充临时表，然后使用@letters+XML中的每个字符串，从临时表中删除所有记录。最后，临时表中只有记录中包含您所需的每个字符串。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1833612|declare+@letters+xml

SET+@letters+=+'&lt;letters&gt;
++++++++++++&lt;letter&gt;a&lt;/letter&gt;
++++++++++++&lt;letter&gt;b&lt;/letter&gt;
++++++++&lt;/letters&gt;'

--+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+'%25'+%2B+@letter+%2B+'%25')
++++END
++++FETCH+NEXT+FROM+Cursor_Letters+INTO+@letter
END

CLOSE+Cursor_Letters
DEALLOCATE+Cursor_Letters

SELECT+*+FROM+#TempResults

DROP+Table+#TempResults
GO|code-block|syntax|javascript|1833613|entityMap^0|1I|C|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

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 <code>@letters XML</code>, 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.

<pre><code>declare @letters xml

SET @letters = '&amp;lt;letters&amp;gt;
 &amp;lt;letter&amp;gt;a&amp;lt;/letter&amp;gt;
 &amp;lt;letter&amp;gt;b&amp;lt;/letter&amp;gt;
 &amp;lt;/letters&amp;gt;'

-- 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 &lt;&gt; -1)
BEGIN
 IF (@@fetch_status &lt;&gt; -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
</code></pre>

blocks|key|1833657|text|我假设您所说的或多或少是认真的，并且您希望在表中找到“包含字母'a‘和字母’b‘”的关键字。这里的一些解决方案给出了另一个问题的答案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1833658|要获取同时包含字母'a‘和'b’的关键字(与包含任何一个字母的关键字相反)，您可以在以下查询中使用'ab‘：|1833659|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+'%25'%2Bsubstring(<matchsetstring>,Nums26.i,1)%2B'%25'+--+adapt+SUBSTRING+to+your+dialect
);|code-block|syntax|javascript|1833660|名为"Nums26“的表应该包含一个列"i”(为提高效率而编制索引)，该列包含1到26中的每一个值(如果您可能尝试匹配多个字母，则可以包含更多值)。见下文。其他人提供的建议适用于大写/小写。但是，如果您的排序规则区分大小写，则不能在此处简单地指定'aAbB‘作为您的排序规则，因为这将请求包含a、A、b和B这四个字符中的每一个的关键字。您可以使用UPPER并匹配'AB’。|1833661|create+table+nums26+(
++i+int+primary+key
);
insert+into+nums26+values+(1);
insert+into+nums26+select+1%2Bi+from+nums26;
insert+into+nums26+select+2%2Bi+from+nums26;
insert+into+nums26+select+4%2Bi+from+nums26;
insert+into+nums26+select+8%2Bi+from+nums26;
insert+into+nums26+select+16%2Bi+from+nums26;|1833662|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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.

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

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

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.

<pre><code>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;
</code></pre>

blocks|key|1538447|text|很可能有一种更优雅的方式，但它确实会查找其中包含字母的每条记录，无论是大写还是小写：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1538448|select+*+from+your_table
where+UPPER(your_field)+like+'%25A%25'
or+UPPER(your_field)+like+'%25B%25'
or+UPPER(your_field)+like+'%25C%25'
or+UPPER(your_field)+like+'%25D%25'
or+UPPER(your_field)+like+'%25E%25'
or+UPPER(your_field)+like+'%25F%25'
or+UPPER(your_field)+like+'%25G%25'
or+UPPER(your_field)+like+'%25H%25'
or+UPPER(your_field)+like+'%25I%25'
or+UPPER(your_field)+like+'%25J%25'
or+UPPER(your_field)+like+'%25K%25'
or+UPPER(your_field)+like+'%25L%25'
or+UPPER(your_field)+like+'%25M%25'
or+UPPER(your_field)+like+'%25N%25'
or+UPPER(your_field)+like+'%25O%25'
or+UPPER(your_field)+like+'%25P%25'
or+UPPER(your_field)+like+'%25Q%25'
or+UPPER(your_field)+like+'%25R%25'
or+UPPER(your_field)+like+'%25S%25'
or+UPPER(your_field)+like+'%25T%25'
or+UPPER(your_field)+like+'%25U%25'
or+UPPER(your_field)+like+'%25V%25'
or+UPPER(your_field)+like+'%25W%25'
or+UPPER(your_field)+like+'%25X%25'
or+UPPER(your_field)+like+'%25Y%25'
or+UPPER(your_field)+like+'%25Z%25'|code-block|syntax|javascript|1538449|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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%'
</code></pre>

I have an SQL table with 11000 keywords in it.

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.

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

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个包含11000个关键字的SQL表。我想要一个查询，可以找到包含某个字母的字段。因此，如果我包含"a“和"b”，查询将选择字段中某处包含字母"a“和字母"b”的所有字段。

问我需要一个查询来返回包含指定字母的每个字段
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我需要一个查询来返回包含指定字母的每个字段EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我需要一个查询来返回包含指定字母的每个字段
EN