Reputation: 15726
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
Reputation: 1172
error persist here
DECLARE @SCOPE varchar = '%bunkering%'
please mention the length of varchar datatype
use
DECLARE @SCOPE varchar(50) = '%bunkering%'
Upvotes: 3
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
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