SQL Server Varchar to VarBinary Conversion

varchar sql-insert varbinary
user2124664 picture user2124664 · Mar 26, 2013 · Viewed 15.2k times · Source

I have to insert the string "johnmelling" value into a table which has the column as
[USERPASS] varbinary NOT NULL.

Please could any one suggest me, what would be the best conversion to insert "johnmelling"?

I tried to to insert as below, 

Insert into table(column1)
Values(CONVERT(varbinary(1), 'johnmelling'))

Then I got the error

Line 1: String or binary data would be truncated.

Thank You,

Answer

GoofyBall picture GoofyBall · May 17, 2013

You are converting to varbinary(1) so your target datatype is varbinary but the integer you have specified in parentheses is 1 which means your datatype will only have a length of 1; you are receiving that error because the length you have allocated to that datatype is too small. The literal, 'johnmelling' is 11 characters but you are trying to store it in a datatype that has a length of 1.

Simply change the integer in parentheses to 11, 50, 255, max or whatever you think is an appropriate length and you won't get that error.

Related questions

What is the difference between varchar and nvarchar?

Is it just that nvarchar supports multibyte characters? If that is the case, is there really any point, other than storage concerns, to using varchars?

Read More
#1071 - Specified key was too long; max key length is 767 bytes

When I executed the following command: ALTER TABLE `mytable` ADD UNIQUE ( `column1` , `column2` ); I got this error message: #1071 - Specified key was too long; max key length is 767 bytes Information about column1 and column2: column1 varchar(20) utf8_general_ci column2 …

Read More
What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

What is meant by nvarchar? What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

Read More