Sql-server – Update a binary field

sql serversql-server-2012varbinary

How to update a binary field from 0x54 to 0x46 (T to F)?

Update dbo.table1
set field = convert(varchar,field,0x46)
where fieldID = '1'

Best Answer

Assuming the field is of BINARY datatype:

UPDATE [dbo].[Table]
SET [field] = CONVERT(BINARY,'F')
WHERE [fieldID] = 1;

Here's my proof of concept:

CREATE TABLE #TempBinTable
 ([Identity] INT
,[BinColumn] BINARY);

INSERT INTO #TempBinTable
VALUES(1,CONVERT(BINARY,'T'));

SELECT [Identity], [BinColumn]
FROM #TempBinTable;
--RESULT
----Identity    | BinColumn
----1           | 0X54

UPDATE #TempBinTable
SET [BinColumn] = CONVERT(BINARY,'F')
WHERE [BinColumn] = CONVERT(BINARY,'T');

SELECT [Identity], [BinColumn]
FROM #TempBinTable;
--RESULT
----Identity    | BinColumn
----1           | 0X46

DROP TABLE #TempBinTable;
Related Question