CODEWITHSUNDEEP
sqlsql-server
Chase Rocker
Chase Rocker

Reputation: 1908

Find rows that contain all words in any order

My application is built in vb.net with SQL Server Compact as the database so I'm unable to use a full-text index.

Here's my data...

MainTable field1

  1. A B C
  2. B G C
  3. X Y Z
  4. C P B

Search term = B C

Expected Results = any combination of the search term = Rows 1, 2, 4

Here's what I'm currently doing...

I'm permuting the search term B C into an array containing %B%C% and %C%B% and inserting those values into field1 of tempTable.

So my SQL looks like this:

SELECT * FROM MainTable INNER JOIN tempTable ON MainTable.field1 LIKE tempTable.field1

In this simple example, it does return the expected results correctly. However, my search term can contain more values. For example 6 search terms B C D E F G when permuted has 720 different values and as more search terms are used, the permutations grow exponentially...which is not good.

Is there a better way to do this?

Upvotes: 0

Views: 106

Answers (1)

msheikh25
msheikh25

Reputation: 578

The following will work for your example above:

Select * from table where field1 like '%[BC]%'

But it will also return strings that contain ONLY "B" or "C". Do you need both characters in any order or one or more?

EDIT: Then the following would work:

Select * from test_data where col1 LIKE '%Apple%' and col1 like '%Dog%'

See the demo here: http://rextester.com/edit/LNDQ49764

Upvotes: 1

Related Questions