CODEWITHSUNDEEP
sqlsql-server
Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

Find exactly text

Text column is NVARCHAR(MAX) type.

ID     Text
001    have odds and modds
002    odds>=12
003    modds
004    odds < 1

How can I search in Text column contains odds and not contain modds

I try:

Select * from MyTable
Where text LIKE '%odds%' AND text NOT LIKE '%modds%'

But result not correct return all. I want return

    ID     Text
    001    have odds and modds
    002    odds>=12
    004    odds < 1

Any ideas? Thanks!

Upvotes: 2

Views: 75

Answers (4)

Mark Schultheiss
Mark Schultheiss

Reputation: 34178

WHERE (text LIKE '%odds%' AND text NOT LIKE '%modds%') 
OR (text LIKE '%odds%odds%')

Some questions regarding how this works. First off, SQL works with "sets" of data so we need a selector (WHERE clause) to create our "set" (or it is the entire table "set" if none is included)

SO here we created two portions of the set.

First we select all the rows that include the value "odds" in them somewhere but do NOT include "modds" in them. This excludes rows that ONLY include "modds" in them.

Second, we include rows where they have BOTH/two values of "odds" in them - the "%" is a wildcard so to break it down starting at the beginning.

  • "'%" anything at the start
  • "'%odds" anything at the start followed by "odds"
  • "'%odds%" anything at the start with anything following that
  • "'%odds%odds" anything at the start with anything following that but has "odds" after that
  • "'%odds%odds%'" anything at the start % with "odds" with anything in between % with "odds" following that with anything at the end %

This works for THIS SPECIFIC case because both the words contain "odds" so the order is NOT specific here. IF we wanted to do that with different words for example "cats", "cats" and "dogs" but JUST "dogs: we would have:

WHERE (mycolumn LIKE '%cats%' AND mycolumn NOT LIKE '%dogs%') 
OR ((mycolumn LIKE '%cats%dogs%') OR (mycolumn LIKE '%dogs%cats%'))

This could also be written like: (has BOTH with the AND)

WHERE (mycolumn LIKE '%cats%' AND mycolumn NOT LIKE '%dogs%') 
OR (mycolumn LIKE '%cats%' AND mycolumn LIKE '%dogs%')

This would catch the values without regard to the order of the "cats" and "dogs" values in the column.

Note the groupings with the parenthesis is not optional for these last two solution examples.

Upvotes: 2

Jonathan Wood
Jonathan Wood

Reputation: 67223

The most flexible and efficient way is to use full-text search. This would create an index for each word in the specified text columns.

This feature is included with (at least some versions of) Microsoft SQL Server.

Upvotes: 0

sarath
sarath

Reputation: 314

Select * from MyTable Where text LIKE '% odds%' or text LIKE 'odds%'

Upvotes: 0

tshoemake
tshoemake

Reputation: 1351

Select * from MyTable
Where text LIKE 'odds%'

Upvotes: 0

Related Questions