CODEWITHSUNDEEP
t-sql
Peter
Peter

Reputation: 489

Like used with parameter don't work correctly

This script should only pick the rows with brickurl = 'http://www.bol.com/something' But it returns nearly all 30 rows, even with totally other texts in it

DECLARE @urlpart nvarchar
set @urlpart='bol.com'
SET @urlpart = '%'+@urlpart + '%'

SELECT * FROM Brick
WHERE BrickUrl like @urlpart

written without parameter like this: 

SELECT * FROM brick
WHERE BrickUrl like '%bol.com%'

returns the 6 rows containing bol.com correctly.

I can't figure out what the reason is, or what I do wrong, do you see it?

Upvotes: 2

Views: 98

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

Try this:

DECLARE @urlpart nvarchar
set @urlpart='bol.com'
SET @urlpart = '%'+@urlpart + '%'
select @urlpart

Result:

(No column name)
%

and then you should try this instead

DECLARE @urlpart nvarchar(128)
set @urlpart='bol.com'
SET @urlpart = '%'+@urlpart + '%'
select @urlpart

Result;

(No column name)
%bol.com%

When not specifying the size of nvarchar you will get size 1.

Upvotes: 4

Related Questions