Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Search for “whole word match” with SQL Server LIKE pattern

Tags:

sql

sql-server

Does anyone have a LIKE pattern that matches whole words only?

It needs to account for spaces, punctuation, and start/end of string as word boundaries.

I am not using SQL Full Text Search as that is not available. I don't think it would be necessary for a simple keyword search when LIKE should be able to do the trick. However if anyone has tested performance of Full Text Search against LIKE patterns, I would be interested to hear.

Edit:

I got it to this stage, but it does not match start/end of string as a word boundary.

where DealTitle like '%[^a-zA-Z]pit[^a-zA-Z]%'  

I want this to match "pit" but not "spit" in a sentence or as a single word.

E.g. DealTitle might contain "a pit of despair" or "pit your wits" or "a pit" or "a pit." or "pit!" or just "pit".

like image 469
mike nelson Avatar asked Mar 26 '11 18:03

mike nelson


People also ask

Which keyword allows you to search for a pattern in a string SQL?

SQL pattern matching allows you to search for patterns in data if you don't know the exact word or phrase you are seeking. This kind of SQL query uses wildcard characters to match a pattern, rather than specifying it exactly. For example, you can use the wildcard "C%" to match any string beginning with a capital C.

How do I match a pattern in SQL?

SQL pattern matching enables you to use _ to match any single character and % to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. Do not use = or <> when you use SQL patterns.


1 Answers

Full text indexes is the answer.

The poor cousin alternative is

'.' + column + '.' LIKE '%[^a-z]pit[^a-z]%' 

FYI unless you are using _CS collation, there is no need for a-zA-Z

like image 115
RichardTheKiwi Avatar answered Sep 21 '22 18:09

RichardTheKiwi