CODEWITHSUNDEEP
sqlsqlitesql-like
Abhinav Sharma
Abhinav Sharma

Reputation: 1265

Match only entire words with LIKE?

So 'awesome document' LIKE '%doc%' is true, because doc is a sub-string. But, I want it to be false while 'awesome doc' or 'doc awesome' or 'awesome doc awesome' should be true. How can I do with with a like?

I'm using sqlite, so I hope I don't have to use something that isn't available.

Upvotes: 32

Views: 37459

Answers (9)

Jason Geiger
Jason Geiger

Reputation: 2112

In more modern SQL Servers you can use the STRING_SPLIT function to do this easily.

The following link splits on spaces.

SELECT * FROM DataTable T WHERE 'doc' IN (SELECT VALUE FROM STRING_SPLIT(T.TextField, ' '))

If you wanted to include commas, periods, question marks, semicolons and exclamation points ie ",.?;!" you can add a translate.

SELECT * FROM DataTable T WHERE 'doc' IN (SELECT VALUE FROM STRING_SPLIT(TRANSLATE(T.TextField, ',.?;!', '     '), ' '))

Upvotes: 1

Sébastien Chandonay
Sébastien Chandonay

Reputation: 69

You can use REGEX with "word boundary" MySql documentation

So, in your example : 'awesome document' REGEXP '\bdoc\b'

Upvotes: 0

Panos K.
Panos K.

Reputation: 61

LIKE is quite limited. I would suggest REGEXP, for example: 

 columnName REGEXP 'doc[;.?!,") ]'

for example: awesome document wouldn't match, but awesome doc, doc awesome, awesome doc awesome will match.

It will also match an existing word in text followed by punctuation characters. You can try any example here: https://regexr.com/

Upvotes: 1

Fandango68
Fandango68

Reputation: 4858

None of the answers worked for me (SQL Server 2008 R2).

But I managed to get it to work using somewhat of the answers shown below..

SELECT * FROM myTable AS v
WHERE v.Note LIKE '%[^a-zA-Z0-9]CONTainers[^a-zA-Z0-9]%'

Note: v.Note is a TEXT data type

This worked for me with the following samples..

  • containers
  • CONTAINERS
  • conTaInERS

But not when the word 'containers' was surrounded by other letters or numerics..

  • 001containers001
  • AAcontainersAA

Which is what the OP asked for in the first place.

Upvotes: 0

Saleh Mosleh
Saleh Mosleh

Reputation: 544

you can just use below condition:

(' '+YOUR_FIELD_NAME+' ') like '% doc %'

it works faster than other solutions.

Upvotes: 13

YetAnotherUser
YetAnotherUser

Reputation: 9346

How about split it into four parts - 

[MyColumn] Like '% doc %' 
OR [MyColumn] Like '% doc' 
OR [MyColumn] Like 'doc %' 
OR [MyColumn] = 'doc'

Edit: An alternate approach (only for ascii chars) could be: 

'#'+[MyColumn]+'#' like '%[^a-z0-9]doc[^a-z0-9]%'

(You may want to take care of any special char as well)

It doesn't look like, but you may want to explore Full Text Search and Contains, in case that's more suitable for your situation.

See: - MSDN: [ ] (Wildcard - Character(s) to Match) (Transact-SQL)

Upvotes: 41

FishBasketGordo
FishBasketGordo

Reputation: 23132

WHERE (@string LIKE 'doc %' OR @string LIKE '% doc' OR @string LIKE '% doc %' OR @string = 'doc')

Upvotes: 8

awestover89
awestover89

Reputation: 1763

You could use some ORs, LIKE '% doc %' OR LIKE 'doc %' OR LIKE '% doc' OR LIKE 'doc'

Upvotes: 1

patapizza
patapizza

Reputation: 2398

What about NOT LIKE '%doc%'?

Upvotes: -6

Related Questions