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.

cjk · Accepted Answer · 2010-05-25 09:37:47Z

4

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)

answered May 25, 2010 at 9:37

cjk

46.6k9 gold badges83 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

+1 - beat me to it! Will keep my answer anyway for it's pointer to reference on similar related gotchas

AdaTheDev · Accepted Answer · 2010-05-25 09:42:11Z

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)

answered May 25, 2010 at 9:42

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges