CODEWITHSUNDEEP
sqlsql-server
Alexei
Alexei

Reputation: 15726

SQL: Clause LIKE work incorrect with variable

MS SQL Server:

first sql:

select id, scope from delivery 
where scope like '%bunkering%'
order by scope desc

As result return 34 rows and in column scope I get ONLY text bunkering.

Nice. It's correct.

Now I want to use variable, like this:

DECLARE @SCOPE varchar = '%bunkering%'
select id, scope from delivery 
where scope like @SCOPE 
order by scope desc

But now I get 38 rows. And now has rows not only with text bunkering. Also has text "mecanisme port"

Why? I need only text bunkering. How I can do this?

Upvotes: 0

Views: 54

Answers (3)

Ravi
Ravi

Reputation: 1172

error persist here

DECLARE @SCOPE varchar = '%bunkering%'

please mention the length of varchar datatype

use 

DECLARE @SCOPE varchar(50) = '%bunkering%'

Upvotes: 3

Alexei
Alexei

Reputation: 15726

I found this solution:

DECLARE @SCOPE varchar = 'bunkering'
select id, scope from delivery 
where scope like '%' + @SCOPE + '%' 
order by scope desc

And it's work fine.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You need a length for the variable declaration:

DECLARE @SCOPE varchar(255) = '%bunkering%';

select id, scope
from delivery 
where scope like @SCOPE 
order by scope desc;

When declaring a variable, varchar with no length defaults to a length of 1. So, you have set the variable @SCOPE to '%', which should match all rows.

Upvotes: 5

Related Questions