why t-sql patindex not work with declared variable?

Question

why this query is not work correctly?

declare @s nvarchar
set @s = 'abcd'
select patindex('%b%', @s)

it is return zero.

Answer 1

It works if you add a size to your initial declaration of the varchar:

declare @s nvarchar(10) 
set @s = 'abcd' 
select patindex('%b%', @s) 

Answer 2

It's because if you don't define the size of the NVARCHAR variable, it will be defaulting to size 1. So @s will only ever contain 'a'.

You need to always be careful, to make sure you define the sizes explicitly as different scenarios behave differently as I blogged here.

So, just change

DECLARE @s NVARCHAR

to (e.g.)

DECLARE @s NVARCHAR(20)