SQLSELECT WHERE字段包含单词

我需要一个选择，它会返回这样的结果：

SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3'

我需要所有结果，即这包括带有word2 word3 word1或word1 word3 word2或三者的任何其他组合的字符串。

所有的词都必须在结果中。

3580244 次浏览
小开
最佳答案

相当慢，但工作方法包括任何的单词：

SELECT * FROM mytableWHERE column1 LIKE '%word1%'OR column1 LIKE '%word2%'OR column1 LIKE '%word3%'

如果您需要所有个单词在场，请使用：

SELECT * FROM mytableWHERE column1 LIKE '%word1%'AND column1 LIKE '%word2%'AND column1 LIKE '%word3%'

如果你想要更快的东西，你需要查看全文搜索，这对于每种数据库类型都是非常具体的。

小开 
SELECT * FROM MyTable WHEREColumn1 LIKE '%word1%'AND Column1 LIKE '%word2%'AND Column1 LIKE  '%word3%'

根据编辑问题将OR更改为AND。

小开 
select * from table where name regexp '^word[1-3]$'

或 

select * from table where name in ('word1','word2','word3')
小开

请注意，如果您使用LIKE来确定一个字符串是否是另一个字符串的子字符串，您必须转义搜索字符串中的模式匹配字符。

如果您的SQL方言支持CHARINDEX，则使用它要容易得多：

SELECT * FROM MyTableWHERE CHARINDEX('word1', Column1) > 0AND CHARINDEX('word2', Column1) > 0AND CHARINDEX('word3', Column1) > 0

另外，请记住，这和接受的答案中的方法只涵盖子字符串匹配而不是单词匹配。因此，例如，字符串'word1word2word3'仍然会匹配。

小开

使用mysql：

辅助函数

-- Split @str by @sep-- Returns all partsCREATE FUNCTION [dbo].[fnSplit] (@sep CHAR(1),@str VARCHAR(512)) RETURNS TABLE AS RETURN (WITH Pieces(pn, start, stop) AS (SELECT1,1,CHARINDEX(@sep, @str)UNION ALLSELECTpn + 1,stop + 1,CHARINDEX(@sep, @str, stop + 1)FROM PiecesWHERE stop > 0)
SELECTpn AS Id,SUBSTRING(@str, start, CASEWHEN stop > 0THEN stop - startELSE 512END) AS DataFROM Pieces)

查询示例

将单词word1、word2、word3搜索到MyTable.Column1：

-- Create a temporal table (the Data size depends on the length of the word)DECLARE @FilterTable TABLE (Data VARCHAR(512))
-- Get different and unique words for the searchINSERT INTO @FilterTable (Data)SELECT DISTINCT S.DataFROM fnSplit(' ', 'word1 word2 word3') S -- Contains words
-- Search into "MyTable" by "Column1"SELECT DISTINCTT.*FROMMyTable T-- Matching recordsINNER JOIN @FilterTable F1 ON T.Column1 LIKE '%' + F1.Data + '%'-- Is some word not present?LEFT JOIN @FilterTable F2 ON T.Column1 NOT LIKE '%' + F2.Data + '%'WHERE-- Is some word not present?F2.Data IS NULL;
小开

如果您使用的是Oracle数据库，那么您可以使用包含查询来实现这一点。包含查询比喜欢查询快。

如果你需要所有的词

SELECT * FROM MyTable WHERE CONTAINS(Column1,'word1 and word2 and word3', 1) > 0

如果你需要任何一句话

SELECT * FROM MyTable WHERE CONTAINS(Column1,'word1 or word2 or word3', 1) > 0

在列上包含类型上下文的需要索引。

CREATE INDEX SEARCH_IDX ON MyTable(Column) INDEXTYPE IS CTXSYS.CONTEXT
小开

如果你只是想找到一个匹配。

SELECT * FROM MyTable WHERE INSTR('word1 word2 word3', Column1)<>0

SQL服务器：

CHARINDEX(Column1, 'word1 word2 word3', 1)<>0

以获得完全匹配。示例：(';a;ab;ac;',';b;')将无法获得匹配。

SELECT * FROM MyTable WHERE INSTR(';word1;word2;word3;', ';'||Column1||';')<>0
小开

而不是SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3'，在这些词之间添加和，例如：

SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 And word2 And word3'

有关详细信息，请参阅CONTAINS（Transact-SQL）。

要选择短语，请使用双引号，例如：

SELECT * FROM MyTable WHERE Column1 CONTAINS '"Phrase one" And word2 And "Phrase Two"'

P. S.：在使用包含关键字之前，您必须先在表上启用全文检索。有关更多详细信息，请参阅开始使用全文搜索。

小开

用途：

SELECT * FROM MyTable WHERE Column1 Like "*word*"

这将显示column1具有包含word的部分值的所有记录。

小开

尝试在SQL服务器的全文索引中使用“Tesari搜索”。如果您有数百万条记录，这比在搜索中使用“%”要好得多。Tesari的内存消耗比其他人少。

尝试搜索此功能：）

小开

最好的方法是在表中的列上制作全文索引用包含代替

SELECT * FROM MyTable WHEREcontains(Column1, N'word1')AND contains(Column1, N'word2')AND contains(Column1, N'word3')
小开

使用“in”代替：

Select *from tablewhere columnname in (word1, word2, word3)
小开 
DECLARE @SearchStr nvarchar(100)SET @SearchStr = ' '


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)SET  @TableName = ''SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGINSET @ColumnName = ''SET @TableName =(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))FROM     INFORMATION_SCHEMA.TABLESWHERE         TABLE_TYPE = 'BASE TABLE'AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableNameAND    OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGINSET @ColumnName =(SELECT MIN(QUOTENAME(COLUMN_NAME))FROM     INFORMATION_SCHEMA.COLUMNSWHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)AND    TABLE_NAME    = PARSENAME(@TableName, 1)AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')AND    QUOTENAME(COLUMN_NAME) > @ColumnName)
IF @ColumnName IS NOT NULL
BEGININSERT INTO #ResultsEXEC('SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2)ENDENDEND
SELECT ColumnName, ColumnValue FROM #Results
DROP TABLE #Results
小开

实现问题中提到的最简单的方法之一是将包含与NEAR或“~”一起使用。例如，以下查询将为我们提供专门包括word1、word2和word3的所有列。

SELECT * FROM MyTable WHERE CONTAINS(Column1, 'word1 NEAR word2 NEAR word3')
SELECT * FROM MyTable WHERE CONTAINS(Column1, 'word1 ~ word2 ~ word3')

此外，CONTAINSTABLE根据“word1”、“word2”和“word3”的接近程度为每个文档返回排名。例如，如果文档包含句子“Word1是word2和word3”，则其排名将很高，因为这些术语比其他文档中的术语更接近彼此。

我们还可以使用proximity_term来查找单词在列短语内它们之间的特定距离内的列。

小开

理想情况下，如果使用SQL服务器全文搜索，这应该在SQL服务器全文搜索的帮助下完成。

但是，如果由于某种原因无法在数据库上运行该功能，这里有一个性能密集型解决方案：

-- table to search inCREATE TABLE dbo.myTable(myTableId int NOT NULL IDENTITY (1, 1),code varchar(200) NOT NULL,description varchar(200) NOT NULL -- this column contains the values we are going to search in)  ON [PRIMARY]GO
-- function to split space separated search string into individual wordsCREATE FUNCTION [dbo].[fnSplit] (@StringInput nvarchar(max),@Delimiter nvarchar(1))RETURNS @OutputTable TABLE (id nvarchar(1000))ASBEGINDECLARE @String nvarchar(100);
WHILE LEN(@StringInput) > 0BEGINSET @String = LEFT(@StringInput, ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),LEN(@StringInput)));SET @StringInput = SUBSTRING(@StringInput, ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput),0), LEN(@StringInput))+ 1, LEN(@StringInput));
INSERT INTO @OutputTable (id)VALUES (@String);END;
RETURN;END;GO
-- this is the search script which can be optionally converted to a stored procedure /function

declare @search varchar(max) = 'infection upper acute genito'; -- enter your search string here-- the searched string above should give rows containing the following-- infection in upper side with acute genitointestinal tract-- acute infection in upper teeth-- acute genitointestinal pain
if (len(trim(@search)) = 0) -- if search string is empty, just return records ordered alphabeticallybeginselect 1 as Priority ,myTableid, code, Description from myTable order by Descriptionreturn;end
declare @splitTable Table(wordRank int Identity(1,1), -- individual words are assinged priority order (in order of occurence/position)word varchar(200))declare @nonWordTable Table( -- table to trim out auxiliary verbs, prepositions etc. from the searchid varchar(200))
insert into @nonWordTable values('of'),('with'),('at'),('in'),('for'),('on'),('by'),('like'),('up'),('off'),('near'),('is'),('are'),(','),(':'),(';')
insert into @splitTableselect id from dbo.fnSplit(@search,' '); -- this function gives you a table with rows containing all the space separated words of the search like in this e.g., the output will be ---  id--------------- infection-- upper-- acute-- genito
delete s from @splitTable s join @nonWordTable n  on s.word = n.id; -- trimming out non-words heredeclare @countOfSearchStrings int = (select count(word) from @splitTable);  -- count of space separated words for searchdeclare @highestPriority int = POWER(@countOfSearchStrings,3);
with plainMatches as(select myTableid, @highestPriority as Priority from myTable where Description like @search  -- exact matches have highest priorityunionselect myTableid, @highestPriority-1 as Priority from myTable where Description like  @search + '%'  -- then with something at the endunionselect myTableid, @highestPriority-2 as Priority from myTable where Description like '%' + @search -- then with something at the beginningunionselect myTableid, @highestPriority-3 as Priority from myTable where Description like '%' + @search + '%' -- then if the word falls somewhere in between),splitWordMatches as( -- give each searched word a rank based on its position in the searched string-- and calculate its char index in the field to searchselect myTable.myTableid, (@countOfSearchStrings - s.wordRank) as Priority, s.word,wordIndex = CHARINDEX(s.word, myTable.Description)  from myTable join @splitTable s on myTable.Description like '%'+ s.word + '%'-- and not exists(select myTableid from plainMatches p where p.myTableId = myTable.myTableId) -- need not look into myTables that have already been found in plainmatches as they are highest ranked-- this one takes a long time though, so commenting it, will have no impact on the result),matchingRowsWithAllWords as (select myTableid, count(myTableid) as myTableCount from splitWordMatches group by(myTableid) having count(myTableid) = @countOfSearchStrings), -- trim off the CTE here if you don't care about the ordering of words to be considered for prioritywordIndexRatings as( -- reverse the char indexes retrived above so that words occuring earlier have higher weightage-- and then normalize them to sequential valuesselect s.myTableid, Priority, word, ROW_NUMBER() over (partition by s.myTableid order by wordindex desc) as comparativeWordIndexfrom splitWordMatches s join matchingRowsWithAllWords m on s.myTableId = m.myTableId),wordIndexSequenceRatings as ( -- need to do this to ensure that if the same set of words from search string is found in two rows,-- their sequence in the field value is taken into account for higher priorityselect w.myTableid, w.word, (w.Priority + w.comparativeWordIndex + coalesce(sequncedPriority ,0)) as Priorityfrom wordIndexRatings w left join(select w1.myTableid, w1.priority, w1.word, w1.comparativeWordIndex, count(w1.myTableid) as sequncedPriorityfrom wordIndexRatings w1 join wordIndexRatings w2 on w1.myTableId = w2.myTableId and w1.Priority > w2.Priority and w1.comparativeWordIndex>w2.comparativeWordIndexgroup by w1.myTableid, w1.priority,w1.word, w1.comparativeWordIndex)sequencedPriority on w.myTableId = sequencedPriority.myTableId and w.Priority = sequencedPriority.Priority),prioritizedSplitWordMatches as ( -- this calculates the cumulative priority for a field valueselect  w1.myTableId, sum(w1.Priority) as OverallPriority from wordIndexSequenceRatings w1 join wordIndexSequenceRatings w2 on w1.myTableId =  w2.myTableIdwhere w1.word <> w2.word group by w1.myTableid),completeSet as (select myTableid, priority from plainMatches -- get plain matches which should be highest rankedunionselect myTableid, OverallPriority as priority from prioritizedSplitWordMatches -- get ranked split word matches (which are ordered based on word rank in search string and sequence)),maximizedCompleteSet as( -- set the priority of a field value = maximum priority for that field valueselect myTableid, max(priority) as Priority  from completeSet group by myTableId)select priority, myTable.myTableid , code, Description from maximizedCompleteSet m join myTable  on m.myTableId = myTable.myTableIdorder by Priority desc, Description -- order by priority desc to get highest rated items on top--offset 0 rows fetch next 50 rows only -- optional paging

提交答案