Henry Henry - 1 year ago 118
SQL Question

how to Concat binary data and nvarchar in SQL

I have data let say idmember like 1234, and want to update my record with adding 0000.
I tried using

'0000' + idmember
and the result is error.

String or binary data would be truncated.

I want the result like this 00001234

Can you guys help me


Answer Source

The error message

String or binary data would be truncated.

most likely is being caused by trying to update the idmember column with a string which cannot fit in the current width. If you have any data there which is already taking up the full width, and you prepend 0000, then it won't fit and you would see this error message.

To fix this, you need to increase the size of your idmember varchar field. You will have to increase it by at least 4 characters, if not more, to make room for the 0000 which you want to prepend.

ALTER TABLE [yourTable]
ALTER COLUMN [idmember] nvarchar(259)  -- or anything greater than 259


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download