CODEWITHSUNDEEP
sqlsql-servert-sqlsql-like
IQn
IQn

Reputation: 153

SQL LIKE operator not showing any result when it should

I got a Vehicle table with lots of info but one of the columns being 'Owner' in a MSSQL table

But one of the owners i can't select when i use LIKE but can if i use =

'Silkeborg Distributionscenter' is the owner (Yes there is a double space in both the table and the param)

So the param is:

DECLARE @Owners nvarchar(MAX) = 'Silkeborg  Distributionscenter'

I tried:

SELECT * FROM Vehicle WHERE @Owners = Owner 
--This gave me all the correct results

Then:

SELECT * FROM Vehicle WHERE @Owners LIKE Owner
--No result

Then:

SELECT * FROM Vehicle WHERE @Owners COLLATE Danish_Norwegian_CI_AS LIKE Owner COLLATE Danish_Norwegian_CI_AS
--No result

I need to use LIKE because i need to be able to select a array of owners at once. So the original select statement look like this

SELECT * FROM Vehicle WHERE ('|' + @Owners + '|' like '%|' + Owner + '|%')
--No result

Any Ideas?

Edit: It all works fine the @Owners is 'Fredericia Distributionscenter' but not 'Silkeborg Distributionscenter', Both of them is within the table.

Is the something work in the 'Silkeborg Distributionscenter' text?

Upvotes: 6

Views: 40743

Answers (5)

Arvo
Arvo

Reputation: 10570

Promoted to an answer as requested.

You have some space characters in the end of your Owner field in Vehicle table. '=' operator skips trailing spaces, 'like' operator doesn't.

Upvotes: 9

tschmit007
tschmit007

Reputation: 7790

the following runs fine, I suspect a missing information

declare @t  table (
    owner nvarchar(50)
)

insert into @t values ('Silkeborg  Distributionscenter'), ('rzrzezer')

declare @owner nvarchar(max) = 'Silkeborg  Distributionscenter'

select * from @t
-- two lines
select * from @t where @owner = owner
-- one line
select * from @t where @owner like owner
-- one line

still works with

set @owner = 'Silkeborg  Distributionscenter|another one'
select * from @t where '|' + @owner + '|'  like '%|' + owner + '|%'
-- one line

but fail if 

insert into @t values ('Silkeborg  Distributionscenter|abc')
select * from @t where '|' + @owner + '|'  like '%|' + owner + '|%'
-- one line, you may expect 2 in this case consider using a many to many relation

Upvotes: 2

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

DECLARE @Owners nvarchar(MAX) = '%Silkeborg  Distributionscenter%'

SELECT * 
FROM   Vehicle 
WHERE  Owner LIKE @Owners

Or you can pass a modified param too, this of use when passing params from front end

Upvotes: 0

Kangkan
Kangkan

Reputation: 15571

Your query is incorrect. Write it like:

SELECT * FROM Vehicle WHERE Owners like '%' + @Owners + '%'

Upvotes: 1

John Woo
John Woo

Reputation: 263683

try interchanging the column and the parameter

SELECT * 
FROM   Vehicle 
WHERE  Owner LIKE '%' + @Owners + '%'

Upvotes: 4

Related Questions