SQL SELECT WHERE 필드에 단어가 포함되어 있습니다.

SQL SELECT WHERE 필드에 단어가 포함되어 있습니다.

다음과 같은 결과를 반환할 선택 항목이 필요합니다.

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

그리고 모든 결과가 필요합니다. 즉, "word2 word3 word1" 또는 "word1 word3 word2" 또는 이 세 개의 다른 조합이 포함된 문자열이 여기에 포함됩니다.

모든 단어는 결과에 포함되어야 합니다.

다소 느리지만 다음과 같은 단어를 포함하는 데 효과적인 방법:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
   OR column1 LIKE '%word2%'
   OR column1 LIKE '%word3%'

모든 단어가 필요한 경우 다음을 사용합니다.

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
  AND column1 LIKE '%word2%'
  AND column1 LIKE '%word3%'

더 빠른 정보를 원하는 경우 전체 텍스트 검색을 조사해야 합니다. 이 검색은 각 데이터베이스 유형에 따라 매우 고유합니다.

주의:LIKE문자열이 다른 문자열의 하위 문자열인지 확인하려면 검색 문자열에서 패턴 일치 문자를 이스케이프해야 합니다.

SQL 방언에서 지원되는 경우CHARINDEX대신 사용하는 것이 훨씬 쉽습니다.

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

또한, 이 방법 및 접수된 답변의 방법은 단어 매칭이 아닌 부분 문자열 매칭만을 대상으로 한다는 점에 유의하시기 바랍니다.예를 들어 문자열은'word1word2word3'여전히 일치합니다.

MySQL의 경우:

보조 기능

-- Split @str by @sep
-- Returns all parts
CREATE FUNCTION [dbo].[fnSplit] (
  @sep CHAR(1),
  @str VARCHAR(512)
) RETURNS TABLE AS RETURN (
  WITH Pieces(pn, start, stop) AS (
    SELECT
      1,
      1,
      CHARINDEX(@sep, @str)
    UNION ALL
    SELECT
      pn + 1,
      stop + 1,
      CHARINDEX(@sep, @str, stop + 1)
    FROM Pieces
    WHERE stop > 0
  )

  SELECT
    pn AS Id,
    SUBSTRING(@str, start, CASE
      WHEN stop > 0
      THEN stop - start
      ELSE 512
    END) AS Data
  FROM 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 search
INSERT INTO @FilterTable (Data)
SELECT DISTINCT S.Data
FROM fnSplit(' ', 'word1 word2 word3') S -- Contains words

-- Search into "MyTable" by "Column1"
SELECT DISTINCT
  T.*
FROM
  MyTable T
  -- Matching records
  INNER 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;

대신SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3', 다음 단어 사이에 & 를 추가합니다.

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

자세한 내용은 CONTENS(Transact-SQL)를 참조하십시오.

구문을 선택할 때는 다음과 같은 큰따옴표를 사용합니다.

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

추신: contains 키워드를 사용하기 전에 테이블에서 Full Text Search를 활성화해야 합니다.자세한 내용은 전체 텍스트 검색 시작을 참조하십시오.

SELECT * FROM MyTable WHERE 
Column1 LIKE '%word1%'
AND Column1 LIKE '%word2%'
AND Column1 LIKE  '%word3%'

변경되었다.OR로.AND질문에 대한 편집을 기반으로 합니다.

오라클 데이터베이스를 사용하는 경우 포함 쿼리를 사용하여 이 작업을 수행할 수 있습니다.포함 쿼리는 유사 쿼리보다 빠릅니다.

만약 당신이 모든 단어가

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

컬럼에 CONTEX 유형의 니즈 인덱스가 포함되어 있습니다.

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

만약 당신이 단지 일치하는 것을 찾고 싶다면.

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

SQL Server:

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

정확히 일치하기 위해서.예:(';a;ab;ac;',';b;')일치하지 않습니다.

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

질문에 언급된 내용을 얻는 가장 쉬운 방법 중 하나는 CONTENS를 NEAR 또는 '~'와 함께 사용하는 것입니다.예를 들어, 다음 쿼리는 word1, word2 및 word3을 포함하는 모든 열을 제공합니다.

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

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

또한 CONTENSTABLE은 "word1, word2" 및 "word3"의 근접성을 기준으로 각 문서의 순위를 반환합니다.예를 들어, 문서에 "word1 is word2 and word3"라는 문장이 포함된 경우 다른 문서보다 용어가 서로 가깝기 때문에 순위가 높아집니다.

또한 proximity_term을 사용하여 단어 사이의 특정 거리 내에 있는 열을 찾을 수 있습니다.

가장 좋은 방법은 표의 열에 전체 텍스트 색인을 만들고 LIKE 대신 포함을 사용하는 것입니다.

SELECT * FROM MyTable WHERE 
contains(Column1, N'word1')
AND contains(Column1, N'word2')
AND contains(Column1, N'word3')

대신 "in"을 사용합니다.

Select *
from table
where columnname in (word1, word2, word3)

SQL Server 풀텍스트 검색을 사용하는 경우 SQL Server 풀텍스트 검색을 사용하는 것이 이상적입니다.

그러나 어떤 이유로 인해 DB에서 작업을 수행할 수 없는 경우 성능 집약적인 솔루션이 있습니다.

-- table to search in
CREATE 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 words
CREATE FUNCTION [dbo].[fnSplit] (@StringInput nvarchar(max),
@Delimiter nvarchar(1))
RETURNS @OutputTable TABLE (
  id nvarchar(1000)
)
AS
BEGIN
  DECLARE @String nvarchar(100);

  WHILE LEN(@StringInput) > 0
  BEGIN
    SET @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 alphabetically
begin
 select 1 as Priority ,myTableid, code, Description from myTable order by Description
 return;
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 search
id varchar(200)
)

insert into @nonWordTable values
('of'),
('with'),
('at'),
('in'),
('for'),
('on'),
('by'),
('like'),
('up'),
('off'),
('near'),
('is'),
('are'),
(','),
(':'),
(';')

insert into @splitTable
select 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 here
declare @countOfSearchStrings int = (select count(word) from @splitTable);  -- count of space separated words for search
declare @highestPriority int = POWER(@countOfSearchStrings,3);

with plainMatches as
(
select myTableid, @highestPriority as Priority from myTable where Description like @search  -- exact matches have highest priority
union
select myTableid, @highestPriority-1 as Priority from myTable where Description like  @search + '%'  -- then with something at the end
union
select myTableid, @highestPriority-2 as Priority from myTable where Description like '%' + @search -- then with something at the beginning
union
select 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 search
select 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 priority
wordIndexRatings as( -- reverse the char indexes retrived above so that words occuring earlier have higher weightage
                     -- and then normalize them to sequential values
select s.myTableid, Priority, word, ROW_NUMBER() over (partition by s.myTableid order by wordindex desc) as comparativeWordIndex
from 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 priority
    select w.myTableid, w.word, (w.Priority + w.comparativeWordIndex + coalesce(sequncedPriority ,0)) as Priority
    from wordIndexRatings w left join
    (
     select w1.myTableid, w1.priority, w1.word, w1.comparativeWordIndex, count(w1.myTableid) as sequncedPriority
     from wordIndexRatings w1 join wordIndexRatings w2 on w1.myTableId = w2.myTableId and w1.Priority > w2.Priority and w1.comparativeWordIndex>w2.comparativeWordIndex
     group 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 value
select  w1.myTableId, sum(w1.Priority) as OverallPriority from wordIndexSequenceRatings w1 join wordIndexSequenceRatings w2 on w1.myTableId =  w2.myTableId
where w1.word <> w2.word group by w1.myTableid
),
completeSet as (
select myTableid, priority from plainMatches -- get plain matches which should be highest ranked
union
select 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 value
select 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.myTableId
order 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

SQL Server의 전체 텍스트 색인에 "Tesarus 검색"을 사용해 보십시오.이것은 수백만 개의 레코드가 있는 경우 검색에 "%"를 사용하는 것보다 훨씬 좋습니다.테사루스는 다른 것들보다 메모리 소비량이 적다.

이 함수의 검색을 시도합니다. : )

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

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         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

        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END   
END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

용도:

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

'하다'라는 기록이 모두 표시됩니다.column1인 값이 되어 .word.

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

또는

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

언급URL : https://stackoverflow.com/questions/14290857/sql-select-where-field-contains-words