SQL SELECT WHERE field contains words

Question

I need a select which would return results like this   SELECT   FROM MyTable WHERE Column1 CONTAINS  word1 word2 word3    And I need all results  i e  this includes strings with  word2 word3 word1  or  word1 word3 word2  or any other combination of the three   All words need to be in the result

User · Answer

SELECT   FROM MyTable WHERE Column1 Like   word     This will display all the records where column1 has a partial value contains word

User · Answer

If you are using Oracle Database then you can achieve this using contains query  Contains querys are faster than like query   If you need all of the words  SELECT   FROM MyTable WHERE CONTAINS Column1  word1 and word2 and word3   1   gt  0   If you need any of the words  SELECT   FROM MyTable WHERE CONTAINS Column1  word1 or word2 or word3   1   gt  0   Contains need  index of type CONTEXT on your column   CREATE INDEX SEARCH IDX ON MyTable Column  INDEXTYPE IS CTXSYS CONTEXT

User · Answer

One of the easiest ways to achiever what is mentioned in the question is by using CONTAINS with NEAR or      For example the following queries would give us all the columns that specifically include word1  word2 and word3   SELECT   FROM MyTable WHERE CONTAINS Column1   word1 NEAR word2 NEAR word3    SELECT   FROM MyTable WHERE CONTAINS Column1   word1   word2   word3     In addition  CONTAINSTABLE returns a rank for each document based on the proximity of  word1    word2  and  word3   For example  if a document contains the sentence   The word1 is word2 and word3   its ranking would be high because the terms are closer to one another than in other documents    One other thing that I would like to add is that we can also use proximity term to find columns where the words are inside a specific distance between them inside the column phrase

User · Answer

Instead of SELECT   FROM MyTable WHERE Column1 CONTAINS  word1 word2 word3   add And in between those words like   SELECT   FROM MyTable WHERE Column1 CONTAINS  word1 And word2 And word3    for details  see here https   msdn microsoft com en-us library ms187787 aspx  UPDATE  For selecting phrases  use double quotes like   SELECT   FROM MyTable WHERE Column1 CONTAINS   Phrase one  And word2 And  Phrase Two     p s  you have to first enable Full Text Search on the table before using contains keyword  for more details  See here https   docs microsoft com en-us sql relational-databases search get-started-with-full-text-search

User · Answer

why not use  in  instead   Select   from table where columnname in  word1  word2  word3

User · Answer

Function   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  gt  0                SELECT            pn AS Id             SUBSTRING  str  start  CASE WHEN stop  gt  0 THEN stop - start ELSE 512 END  AS Data       FROM            Pieces      Query   DECLARE  FilterTable TABLE  Data VARCHAR 512     INSERT INTO  FilterTable  Data   SELECT DISTINCT S Data  FROM fnSplit       word1 word2 word3   S -- Contains words   SELECT DISTINCT       T    FROM       MyTable T       INNER JOIN  FilterTable F1 ON T Column1 LIKE       F1 Data             LEFT JOIN  FilterTable F2 ON T Column1 NOT LIKE       F2 Data        WHERE       F2 Data IS NULL

User · Answer

best way is making full-text index on a column in table  and use contain instead of LIKE  SELECT   FROM MyTable WHERE  contains Column1   N word1    AND contains Column1   N word2    AND contains Column1   N word3

User · Answer

SELECT   FROM MyTable WHERE  Column1 LIKE   word1   AND Column1 LIKE   word2   AND Column1 LIKE    word3     Changed OR to AND based on edit to question

User · Answer

try to use the  tesarus search  in full text index in MS SQL Server  This is much better than using     in search if you have millions of records  tesarus have a small amount of memory consumption than the others  try to search this functions

User · Answer

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

User · Answer

This should ideally be done with the help of sql server full text search if using  However  if you can t get that working on your DB for some reason  here is a performance intensive solution  -  -- 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   gt  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  gt  w2 Priority and w1 comparativeWordIndex gt 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  lt  gt  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

User · Answer

select   from table where name regexp   word 1-3      or   select   from table where name in   word1   word2   word3

User · Answer

Note that if you use LIKE to determine if a string is a substring of another string  you must escape the pattern matching characters in your search string   If your SQL dialect supports CHARINDEX  it s a lot easier to  use it instead   SELECT   FROM MyTable WHERE CHARINDEX  word1   Column1   gt  0   AND CHARINDEX  word2   Column1   gt  0   AND CHARINDEX  word3   Column1   gt  0   Also  please keep in mind that this and the method in the accepted answer only cover substring matching rather than word matching  So  for example  the string  word1word2word3  would still match

User · Answer

If you just want to find a match   SELECT   FROM MyTable WHERE INSTR  word1 word2 word3  Column1  lt  gt 0   SQL Server    CHARINDEX Column1   word1 word2 word3   1  lt  gt 0   To get exact match  Example    a ab ac     b    will not get a match   SELECT   FROM MyTable WHERE INSTR   word1 word2 word3        Column1       lt  gt 0

User · Answer

Rather slow  but working method to include any of words   SELECT   FROM mytable WHERE column1 LIKE   word1      OR column1 LIKE   word2      OR column1 LIKE   word3     If you need all words to be present  use this   SELECT   FROM mytable WHERE column1 LIKE   word1     AND column1 LIKE   word2     AND column1 LIKE   word3     If you want something faster  you need to look into full text search  and this is very specific for each database type

[sql] SQL SELECT WHERE field contains words

Examples related to sql

Examples related to select