SQL: Find rows where Column contains all of the given words

sql server contains
sql check if string contains letters
sql check if column contains value
sql contains multiple words
select from table where column contains value
mysql select where field contains
sql find a word in string
sql select rows containing part of string

I have some column EntityName, and I want to have users to be able to search names by entering words separated by space. The space is implicitly considered as an 'AND' operator, meaning that the returned rows must have all of the words specified, and not necessarily in the given order.

For example, if we have rows like these:

  1. abba nina pretty balerina
  2. acdc you shook me all night long
  3. sth you are me
  4. dream theater it's all about you

when the user enters: me you, or you me (the results must be equivalent), the result has rows 2 and 3.

I know I can go like:

WHERE Col1 LIKE '%' + word1 + '%'
  AND Col1 LIKE '%' + word2 + '%'

but I wanted to know if there's some more optimal solution.

The CONTAINS would require a full text index, which (for various reasons) is not an option.

Maybe Sql2008 has some built-in, semi-hidden solution for these cases?

The only thing I can think of is to write a CLR function that does the LIKE comparisons. This should be many times faster.

Update: Now that I think about it, it makes sense CLR would not help. Two other ideas:

1 - Try indexing Col1 and do this:

WHERE (Col1 LIKE word1 + '%' or Col1 LIKE '%' + word1 + '%')
  AND (Col1 LIKE word2 + '%' or Col1 LIKE '%' + word2 + '%')

Depending on the most common searches (starts with vs. substring), this may offer an improvement.

2 - Add your own full text indexing table where each word is a row in the table. Then you can index properly.

SQL SELECT WHERE field contains words, If you need all words to be present, use this: If you want something faster, you need to look into full text search, and this is very specific for each database type. You can use AND NOT, for example, to find the rows that contain "bagel" but do not contain "cream cheese." In contrast, FREETEXT and FREETEXTTABLE treat the Boolean terms as words to be searched. For information about combining CONTAINS with other predicates that use the logical operators AND, OR, and NOT, see Search Condition (Transact-SQL) .

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 > 0
      )

      SELECT
           pn AS Id,
           SUBSTRING(@str, start, CASE WHEN stop > 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.Col1 LIKE '%' + F1.Data + '%'
      LEFT JOIN @FilterTable F2 ON T.Col1 NOT LIKE '%' + F2.Data + '%'
 WHERE
      F2.Data IS NULL

Source: SQL SELECT WHERE field contains words

CONTAINS (Transact-SQL), Transact-SQL reference for the CONTAINS language element. of a Transact- SQL SELECT statement to perform SQL Server full-text search on Is the name of a full-text indexed column of the table specified in the FROM clause. For the query to return any rows, property_name must be specified in the� CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types. CONTAINS can search for: A word or phrase. The prefix of a word or phrase.

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Query with Full-Text Search, All entries in the column that contain text beginning with the specified For example, the prefix term "light bread*" will find rows with text of� Oracle SQL : select * from MY_TABLE where REGEXP_LIKE (company , 'Microsodt industry | goglge auto car | oracles database') company - is the database column name. results - this SQL will show you if company column rows contain one of those companies (OR phrase) please note that : no wild characters are needed, it's built in.

You're going to end up with a full table scan anyway.

The collation can make a big difference apparently. Kalen Delaney in the book "Microsoft SQL Server 2008 Internals" says:

Collation can make a huge difference when SQL Server has to look at almost all characters in the strings. For instance, look at the following:

SELECT COUNT(*) FROM tbl WHERE longcol LIKE '%abc%'

This may execute 10 times faster or more with a binary collation than a nonbinary Windows collation. And with varchar data, this executes up to seven or eight times faster with a SQL collation than with a Windows collation.

Find tables with specific word in name in SQL Server database , Reduce a table that contains multiple columns down to a single column for use To simulate the select unique col_1, col_2 of SQL you can use DataFrame. I need to retrieve all rows from a table where 2 columns combined are all different. This is If there are multiple rows for a given combination of inputs, only the first � 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.

WITH Tokens AS(SELECT 'you' AS Token UNION ALL SELECT 'me')
SELECT ...
FROM YourTable AS t
WHERE (SELECT COUNT(*) FROM Tokens WHERE y.Col1 LIKE '%'+Tokens.Token+'%') 
 = 
(SELECT COUNT(*) FROM Tokens) ;

Select distinct on two column with multiple columns returned, SELECT DISTINCT ON eliminates rows that match on all the specified expressions. SELECT The noise word DISTINCT can be added to explicitly specify eliminating duplicate rows. You must have SELECT privilege on each column used in a SELECT command. The FROM clause can contain the following elements:. ON CONTAINS(P.Name, K.Word) One problem I can see with this though is that if the name contains 2 of the words you may get it twice (though it will also be a problem with Kostantin's version). Another advantage is that FTS supports placing a list of columns in the search term (or even * for all FTS registered columns in the table).

Documentation: 9.5: SELECT, I want to see if a table contains any sub-string of a given string. Let's say I have a string somedomain.com. In database I have: blabladomain� Get all rows in a Pandas DataFrame containing given substring Let’s see how to get all rows in a Pandas DataFrame containing given substring with the help of different examples. Code #1: Check the values PG in column Position

Find if any of the rows partially match a string, This tutorial shows you how to use the SQL Server IN operator to check whether a that allows you to test whether a specified value matches any value in a list. All the values must have the same type as the type of the column or expression . Note that if a list contains NULL , the result of IN or NOT IN will be UNKNOWN . Hi, Hello all, I am new to SQL . Case : I have a column in table which contains strings. These strings contain alphabets as well numeric digits, special characters,. I want to retrive rows which contains only alphabets in string.

SQL Server IN Operator: Match Any Value in a List or a Subquery, Sql LIKE command to match different strings and chars in a record field of mysql table. The above result shows that we will get all the names where John word is present in the To display the records which does not have specific word John as the what if i would like to find person's name which contain 2 words only? The WHERE clause enables you to retrieve only rows from a table that satisfy a condition. WHERE clauses can contain any of the columns in a table, including columns that are not selected. Using a Simple WHERE Clause The following example uses a WHERE clause to find all countries that are in the continent of Europe and their populations:

Comments
  • I'm just curious to know the reasons why the full text index solution is off the table. That's certainly the way I'd want to go here.
  • Sorry for a late reply - it's not working for us because it doesn't support searches like the one I used as an example in question ('%term%' - the search that's not limited to separated words, but even words that only contain the term in it). And, furthermore, the SqlServer is on a clustered machine with shared network drives, and any additional instalations are frozen (and we need to install full-text search, because admins didn't include it when installing) - they assure us it's a hell to do additional instalations to nodes... so that's why it's off the table...
  • Even though I was against it at first, it seems that's the best solution so far...
  • After I've tried it, I want to add an update to this - it's just incredibly slow... if the 'like' method finishes in 10 seconds, this CLR function needs ...well I don't know - I just stopped it after 20 mins... so this solution is shelved as well...
  • the 1. doesn't cover the cases where the rows don't start with the search word (but it is faster because it can use index in that case). The 2. is ok, and we we're already thinking about it. Thanks for the updates!
  • @veljkoz: That is incorrect, #1 does cover substring matches, see the OR clause.