2

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!

Nguyễn Hải Triều
  • 1,454
  • 1
  • 8
  • 14

4 Answers4

2
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.

Mark Schultheiss
  • 32,614
  • 12
  • 69
  • 100
  • the %odds%odds% is tricky and clean, it solved the odds and modds contained for every line. Where can I see this types of documentation sir? – ken lacoste Dec 04 '15 at 06:11
  • I search text in list stored procedures – Nguyễn Hải Triều Dec 04 '15 at 07:59
  • @kenlacoste - I pretty much just pulled that out of my head however you can use several SQL Server related sites for some more complex - start here with this site: www.stackoverflow.com of course others are: http://www.sqlservercentral.com/ or perhaps microsoft https://msdn.microsoft.com/en-us/library/mt590198(v=sql.1).aspx and the dba site: http://dba.stackexchange.com/ – Mark Schultheiss Dec 04 '15 at 15:17
  • The key here to this was/is actually the question which was asked pretty well: including the output expected example and the code that was tried first. Things are always some pattern. Solve the issue, then see how you can simplify what you wrote - get rid of stuff you DON'T need, then use that. – Mark Schultheiss Dec 04 '15 at 15:21
  • I knew what he's asking and my answer produced 1 tabled-function and a scalar-valued function as workaround and I still can't get it, the results are still limited to by exact words (odds), I just don't have a neat and clean solution as yours. I can even suspect that when you have this type of documentations / knowledge, you're one of the MS SQL developers to know such undocumented workaround. – ken lacoste Dec 05 '15 at 07:31
  • @kenlacoste - this is not really "undocumented" is it more "how SQL works" - added some notes to explain how it works. – Mark Schultheiss Dec 06 '15 at 19:06
0
Select * from MyTable
Where text LIKE 'odds%'
tshoemake
  • 1,311
  • 1
  • 17
  • 28
0
Select * from MyTable Where text LIKE '% odds%' or text LIKE 'odds%'
sarath
  • 314
  • 6
  • 15
0

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.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466