CODEWITHSUNDEEP
sqlmatch
user455100
user455100

Reputation: 151

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

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] + '%'

Upvotes: 2

Views: 1781

Answers (3)

Byron Whitlock
Byron Whitlock

Reputation: 53921

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.

Upvotes: 0

Denis Valeev
Denis Valeev

Reputation: 6015

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.

Upvotes: 2

snoofkin
snoofkin

Reputation: 8895

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

Upvotes: 0

Related Questions