how to Concat binary data and nvarchar in SQL

Question

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

Thanks

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Aug 21 '16 at 09:41
@marc_s I'm assuming SQL Server, given that a Google search of the verbatim error message seemed to bring up SQL Server results. — Tim Biegeleisen, Aug 21 '16 at 09:42

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

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

Reference

edited May 23 '17 at 11:51

Community

1
1

answered Aug 21 '16 at 09:21

Tim Biegeleisen

387,723
20
200
263

idmember record only 6 digit, and column size 255. I just want to add 0000 to my all idmember – Henry Aug 21 '16 at 09:26
@Henry What does `SELECT MAX(LEN([idmember])) FROM [yourTable]` show you? – Tim Biegeleisen Aug 21 '16 at 09:31
my max character is 6 – Henry Aug 21 '16 at 09:36
I got your point :) my mistakes, Yes I increase the characters – Henry Aug 21 '16 at 09:39

how to Concat binary data and nvarchar in SQL

1 Answers1