CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Kuntady Nithesh
Kuntady Nithesh

Reputation: 11721

Sql query to exact matches of the word

My query is like this 

declare @searchWord varchar(100)
set @searchWord = 'carb9' 
declare @tableWord varchar(100)
set @tableWord = 'Racing Carb9 uretor'
--set @tableWord =  'carb9'

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

This query works when @tableWord is 'Racing Carb9 uretor' but doesnt work when @tableword is just 'carb9' . It should work in both condition . What i am doing wrong?

Upvotes: 0

Views: 317

Answers (2)

roman
roman

Reputation: 117380

You could add condition @tableWord = @searchWord to your query. At the moment, none of your conditions could return true for that case

declare @searchWord varchar(100)
set @searchWord = 'carb9' 
declare @tableWord varchar(100)
set @tableWord = 'Racing Carb9 uretor'
--set @tableWord =  'carb9'

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

Upvotes: 2

user2408578
user2408578

Reputation: 464

@tableWord='carb9' will not match any of the pattern of your where clause,

Upvotes: 0

Related Questions