SQL SELECT WHERE field contains words

sql select
Mario M picture Mario M · Jan 12, 2013 · Viewed 2.2M times · Source

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.

Answer

mvp picture mvp · Jan 12, 2013

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.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More
Convert INT to VARCHAR SQL

I am using Sybase and I am doing a select which returns me a column called "iftype", but its type is int and I need to convert into varchar. When I try to do the select without the convert function …

Read More