Search for "Whole Word Match" with SQL Server Like Pattern

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

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

using 'like' to find whole words in sql

Your T-SQL query won’t work as posted. I had to modify it to obtain the
desired results:

select * from mytable
where ' ' + title + ' ' like '% white %'

Note the order of the spaces and percentage wildcards within the single
quotes. Also it’s SELECT not SEARCH.

To get it working in MySQL, the query needs to be rewritten as:

select * from mytable
where concat(' ', title, ' ') like '% white %';

MySQL uses the CONCAT() function to implement string concatenation while
T-SQL traditionally uses plus sign, + to implement string concatenation.

MS SQL server has supported the CONCAT
function since SQL Server 2012 so if you’re using a recent version, this
second query should be the only one you need. Otherwise, I don’t think there’s
a simple version which will work with both types of DBMS.

how to search for specific whole words within a string , via SQL, compatible with both HIVE/IMPALA

You can add word boundary \\b to match only exact words:

rlike '(?i)\\bFECHADO\\b|\\bCIERRE\\b|\\bCLOSED\\b'

(?i) means case insensitive, no need to use UPPER.

And the last alternative in your regex pattern is REVISTO. NORMAL.

If dots in it should be literally dots, use \\.

Like this: REVISTO\\. NORMAL\\.

Dot in regexp means any character and should be shielded with two backslashes to match dot literally.

Above regex works in Hive. Unfortunately I have no Impala to test it

Match only entire words with LIKE?

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)

Search for whole word match in MySQL

You can use REGEXP and the [[:<:]] and [[:>:]] word-boundary markers:

SELECT *
FROM table 
WHERE keywords REGEXP '[[:<:]]rid[[:>:]]'

Update for 2020: (actually 2018+)

MySQL updated its RegExp-Engine in version 8.0.4, so you will now need to use the "standard" word boundary marker \b:

SELECT *
FROM table 
WHERE keywords REGEXP '\\brid\\b'

Also be aware that you need to escape the backslash by putting a second backslash.

Find word with whitespace SQL pattern matching

I want to check if Veg is available as a single word in the dat

CREATE TABLE T(
  Value VARCHAR(45)
);

INSERT INTO T VALUES
('FooVeg Bar'), --not single word
('Foo Veg Bar'), --in the middle
('Veg Foo Bar'), --in the begining
('Bar Foo Veg'); --in the end

Since it can be anywhere, you can use

SELECT *
FROM T
WHERE LOWER(Value) LIKE '% veg %'
      OR
      RIGHT(LOWER(Value), 4) = ' veg'
      OR
      LEFT(LOWER(Value), 4) = 'veg '

OR

SELECT *
FROM T 
WHERE Value LIKE '% veg %'
      OR
      Value LIKE '% veg'
      OR
      Value LIKE 'veg %'
COLLATE Latin1_General_CI_AI

Regular Expression (RegEx) to find a particular word in any part of a string but has a character limit

Generally, the like operator has limited functionality, certainly less than regexp. However, what you need can be done by

1. Appending a space to the beginning and end of your search string to ensure it's a different word
2. Doing the same to the target string, so that it will also be matched in the edges:

where ' '+folder+' ' like '% old %'

MySQL Whole Word Match – Multiple words

You are getting different results because the two regexes are not identical.

(^| ) means : either the beginning of the string or a space (( |$) has the same meaning at end of string).

[[:<:]] and [[:>:]] are word boundaries : conceptually this refers to characters that separate words, and usually regex engines interpret it as something like : anything but a digit, a letter or an underscore.

So basically the first pattern is more restrictive than the second (space, beginning and end of string are word boundaries, but there are others).

If you have more than one keyword to search for, you would need to repeat the regex matches, like :

WHERE 
    ".$source." RLIKE '[[:<:]]".$keyword1."[[:>:]]'
    OR ".$source." RLIKE '[[:<:]]".$keyword2."[[:>:]]'

Or create a new regex by combining the keywords :

WHERE 
    ".$source." RLIKE '[[:<:]](".$keyword1.")|(".$keyword2.")[[:>:]]'

NB : for search requirement, you should consider using MySQL Full Text Search, which are primarily built for the purpose of searching for full words (there are pre-requisites, though).

---

Related Topics