0

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'
  • There was clearly a typo in the code sample (a quotation mark before the 0x46), which I took the liberty to fix because the main issue here appears to be the wrong usage of CONVERT. – Andriy M Aug 5 '16 at 20:07
1

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;
|improve this answer|||||
  • UPDATE ... SET [BinColumn] = 0x46 WHERE [BinColumn] = 0x54 ; should work, too, right? – ypercubeᵀᴹ Apr 26 '17 at 12:07
  • Yeah absolutely, I don't see why not! The assumption by me back when I answered this was knowledge of hex codes off hand. – MguerraTorres Apr 26 '17 at 12:17
1

Declare a variable with the correct datatype, assign the value, and update with that value.

-- Asume VARBINARY(10) is the correct datatype
DECLARE @updateValue VARBINARY(10) = 0x46;

UPDATE dbo.table1
SET field = @updateValue
WHERE fieldID = 1;
GO

If you want an inline update, do this:

-- Asume VARBINARY(10) is the correct datatype
UPDATE dbo.table1
SET field = CONVERT(VARBINARY(10), 'F')
WHERE fieldID = 1;
GO
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.