Find word with whitespace SQL pattern matching

Question

I have a column with data Oil 76072 KTC Vegetable (Plastic Drum) 20ltr

and I want to check if Veg is available as a single word in the data. It can be first, last or a word in between like KTC

Query I have below returns data for Veg and KTC both.

SELECT
    Item,
    Description,
    SearchBox,
    Ranking,
    'Part Word Match' as SearchType 
FROM
    [HypEcommerce].[dbo].[ProductSearch]
WHERE (
    [searchbox] like '%_' + trim(ltrim('veg')) +'_%'
    OR [searchbox] like '%_'+ rtrim(ltrim('veg'))+ ''
    OR [searchbox] like ''+ rtrim(ltrim('veg')) +'_%'    
)

I'm really not following, why can't you just do UPPER(searchBox) LIKE ' % VEG %' — Error_2646, Dec 06 '18 at 21:44
What is the syntax for parsing words? Do you want all of the following to match: `'Veg out!'`, `'Living on the veg!'`, `'Squirrel (VEG) purple$'`, `'Version: X9.VEG;2.1'`, `'/Foods/Veg/Eggplant/Fried'`? The pattern might be `empty or non-alphanumeric`, `target string`, `empty or non-alphanumeric`. — HABO, Dec 06 '18 at 21:58

Ilyes · Accepted Answer · 2018-12-06T22:17:40.530

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

score 0 · Answer 2 · answered Dec 06 '18 at 21:44

0

If I understand correctly, you want:

WHERE ' ' + searchbox] + ' ' like '% ' + ' + trim(ltrim('veg')) + ' %'

answered Dec 06 '18 at 21:44

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Dec 06 '18 at 21:49

You need to check 3 cases start, in the middle or at the end with LIKE operator:

SELECT
    Item,
    Description,
    SearchBox,
    Ranking,
    'Part Word Match' as SearchType 
FROM
    [HypEcommerce].[dbo].[ProductSearch]
WHERE (
    [searchbox] like 'veg %' or 
    [searchbox] like '% veg %' or 
    [searchbox] like '% veg'    
)

I guess there would be other cases like (veg ...).
or using LOWER([searchbox]) in case there is also Veg or VEG

score 0 · Answer 4 · answered Dec 06 '18 at 21:55

This would have been easy with a regular expression, unfortunately SQL Server does not support regexps.

So you have to use LIKE, with the 3-branches test. I just updated your query to avoid the ulgy repeated rtrim/ltrim :

SELECT
    Item,
    Description,
    SearchBox,
    Ranking,
    'Part Word Match' as SearchType 
FROM
    [HypEcommerce].[dbo].[ProductSearch]
WHERE (
       veg like        [searchbox] + ' %' -- match at beginning
    OR veg like '% ' + [searchbox] + ' %' -- match in the middle
    OR veg like '% ' + [searchbox]        -- match at end 

);

Find word with whitespace SQL pattern matching

4 Answers4