Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert 0 in varbinary(max) field

I am trying to insert 0 value in varbinary max to test some results. I tried casting '' to binary, tried entering 0 to it but it converts it to 0x00.

Is there a way to enter just 0 for particular value?

like image 968
NoviceMe Avatar asked Oct 18 '13 20:10

NoviceMe


People also ask

What is the max size of Varbinary?

varbinary [ ( n | max) ] Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes.

What datatype is Varbinary?

Store raw-byte data, such as IP addresses, up to 65000 bytes. The BINARY and BINARY VARYING (VARBINARY) data types are collectively referred to as binary string types and the values of binary string types are referred to as binary strings.

How many bytes is a Varbinary?

Note: The default size of VARBINARY is 1 byte.

What is the difference between varchar and Varbinary?

The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings. M represents the maximum column length in bytes. It contains no character set, and comparison and sorting are based on the numeric value of the bytes.


1 Answers

If you're looking to represent an empty byte sequence, set its value to 0x

INSERT INTO MyTable(MyBinary) VALUES (0x)

For example:

DECLARE @myTable TABLE (
    binaryField VARBINARY(MAX) NOT NULL
)

INSERT INTO @myTable(binaryField) VALUES(0x) -- 0 bytes
INSERT INTO @myTable(binaryField) VALUES(0x11223344) -- 4 bytes

SELECT *, len(binaryField) FROM @myTable
like image 126
Matthew Avatar answered Oct 11 '22 22:10

Matthew