SQL Alternative to Like to return exact matches (ie. ?

Question

I need this to return an exact match while still being able to use the wild card.

So when searching for 'carb' i want to return a value such as 'Racing Carb' but i do not want to return a value such as 'Racing Carburetor'

This is what I have now...

SELECT  I.SKU, I.[Description]
FROM    Inventory AS I  
INNER JOIN MisspelledWords AS M
ON I.[Description] like   '%' + M.[Word] + '%'

Answer 1

Dunno about mysql or ms-sql, but you can use regexp_like function in oracle.

Answer 2

Try this:

ON I.[Description] like '%[^a-z]' + M.[Word] + '[^a-z]%'

Update

declare @word varchar(100)
set @word = 'carb' 

select 1 
where  'Racing Carburetor' like '%[^a-z]' + @word + '[^a-z]%'

Returns nothing, which means that 'Racing Carburator' isn't getting selected.

Update 2

declare @searchWord varchar(100), @tableWord varchar(100)
select @searchWord = 'carburetor' 
,@tableWord = 'Racing Carburetor'

select 1 
where  @tableWord like '%[^a-z]' + @searchWord + '[^a-z]%'
or @tableWord like @searchWord + '[^a-z]%'
or @tableWord like '%[^a-z]' + @searchWord 

This one deals with marginal cases.

Answer 3

try

(field like '%carb' or field like '%carb %')

This will match racing carb and carb for racing but not racing carburator or carburator for racing.