SQL Server/T-SQL Tutorial/Data Types/VARBINARY

Binary Data Types

   <source lang="sql">

6> CREATE TABLE MyTable ( 7> Id int, 8> BinData varbinary(8000), 9> Diagram varbinary(max)) 10> 11> go 1> 2> Update MyTable 3> Set BinData = 0x82A7210B 4> where Id = 121131 5> GO (0 rows affected) 1> 2> 3> drop table MyTable; 4> GO 1></source>

CONVERT (varbinary(8), @today)

   <source lang="sql">

31> DECLARE @today datetime 32> SELECT @today = current_timestamp 33> 34> SELECT @today 35> SELECT CONVERT (varbinary(8), @today) 36> SELECT CONVERT (int, SUBSTRING (CONVERT (varbinary(8), @today), 1, 4)) 37> SELECT CONVERT (int, SUBSTRING (CONVERT (varbinary(8), @today), 5, 4)) 38> GO

---

2008-08-17 13:14:21.513 (1 rows affected)

---

---

---

0x00009AFB00DA2D56

(1 rows affected)

---

     39675

(1 rows affected)

---

  14298454

(1 rows affected) 1></source>

VARBINARY(MAX)

   <source lang="sql">

2> IF OBJECT_ID("dbo.CustomerData") IS NOT NULL 3> DROP TABLE dbo.CustomerData; 4> GO 1> 2> CREATE TABLE dbo.CustomerData 3> ( 4> custid INT NOT NULL PRIMARY KEY, 5> txt_data VARCHAR(MAX) NULL, 6> ntxt_data NVARCHAR(MAX) NULL, 7> binary_data VARBINARY(MAX) NULL 8> ); 9> GO 1> 2> INSERT INTO dbo.CustomerData(custid, txt_data) 3> VALUES(102, "Customer 102 text data"); 4> GO 1> 2> drop table CustomerData; 3> GO</source>

VARBINARY type variable

   <source lang="sql">

6> DECLARE @sql AS NVARCHAR(4000), 7> @b AS VARBINARY(1000), @s AS VARCHAR(2002); 8> SET @s = "0x0123456789abcdef"; 9> 10> IF @s NOT LIKE "0x%" OR @s LIKE "0x%[^0-9a-fA-F]%" 11> BEGIN 12> RAISERROR("Possible SQL Injection attempt.", 16, 1); 13> RETURN; 14> END 15> 16> SET @sql = N"SET @o = " + @s + N";"; 17> EXEC sp_executesql 18> @stmt = @sql, 19> @params = N"@o AS VARBINARY(1000) OUTPUT", 20> @o = @b OUTPUT; 21> 22> SELECT @b; 23> GO

---

---

---

0x0123456789ABCDEF

1> 2></source>