CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Kliver Max
Kliver Max

Reputation: 5299

How to cast varchar in varbinary?

I try to update column value by appending varchar strings.

MyTable{
   Id int,
   MyValueColumn varbinary(max),
   MyParamColumn varchar(50)
}

how i append:

'{"ZoneId":'+cast ([MyValueColumn] as varchar)+', "ZoneName":"'+[MyParamColumn]+'"}'

And this return correct value:

    '{"ZoneId":1018, "ZoneName":"szz"}'

But now i cast result in varbinary and cast it again in varchar (for check varbinary correcy):

    cast (cast('{"ZoneId":'+cast ([MyValueColumn] as varchar)+', "ZoneName":"'+[MyParamColumn]+'"}' as varbinary) as varchar)

and result:

'{"ZoneId":1018

Whats can be wrong?

Upvotes: 0

Views: 4259

Answers (1)

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

When you are using cast to varbinary without explicit specification of length default length is 30.

So some trimming of your data can occurs if you're using varbinary, not varbinary(n) or varbinary(max) explicitly.

See MSDN for reference.

NOTE:

Also you have missed ' in your query: cast (cast({"ZoneId":' should be cast (cast('{"ZoneId":'

Update

I've created simple example:

declare @text varchar(50)
select @text = '{"ZoneId":'+cast (1018 as varchar)+', "ZoneName":"'+'szz'+'"}'

select cast(cast(@text as varbinary) as varchar)
select cast(cast(@text as varbinary(max)) as varchar(max))

it gives you trimmed text in first cast and original untrimmed text when we're using varbinary(max)

Upvotes: 2

Related Questions