Member Avatar for krunal1986

hi,

how to convert db table column datatype from varchar to binary(16) in sql server 2005?

thanks

  • 2 Contributors
  • 3 Replies
  • 341 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

As suggested, a staged migration is safest: add a new binary column, validate/clean the varchar values, populate the new column with explicit conversions, test, then drop/rename the old column. Converting char->binary requires valid hex (even number of hex digits) or a valid GUID string; SQL Server will error on bad input. (learn.microsoft.com)

Quick validation to find rows that will fail a hex/GUID conversion (adjust names as needed):

SELECT id, OldCol
FROM dbo.MyTable
WHERE OldCol IS NOT NULL
  AND (LEN(REPLACE(OldCol,'-','')) <> 32
       OR PATINDEX('%[^0-9A-Fa-f]%', REPLACE(OldCol,'-','')) > 0);

This finds values that are not 32 hex chars after removing hyphens (common GUID/hex formats) so they can be fixed before converting. Errors like failed GUID conversion are common if bad strings remain. (learn.microsoft.com)

Two common safe conversion patterns (use VARBINARY(16) first to avoid truncation/padding surprises):

  • GUID text (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx) -> binary(16):
UPDATE dbo.MyTable
SET NewBin = CAST(CAST(OldCol AS UNIQUEIDENTIFIER) AS BINARY(16))
WHERE /* same validation condition as above */;
  • 32-digit hex text (no 0x) -> binary(16):
UPDATE dbo.MyTable
SET NewBin = CONVERT(BINARY(16), REPLACE(OldCol,'-',''), 2)
WHERE /* same validation condition as above */;

Casting via UNIQUEIDENTIFIER is convenient for typical GUID formats; CONVERT with style 1/2 accepts hex input (style 1 expects '0x', style 2 does not). (documentation.help)

On SQL Server 2005 there is no TRY_CONVERT, so pre-validate rows or apply updates in small batches/row-by-row and keep backups. After verification, enforce constraints, then rename or remove the old column (for example use EXEC sp_rename to swap names). Always test on a copy before touching production. (learn.microsoft.com)

Recommended Answers

All 3 Replies

Member Avatar for krunal1986

hi pritaeas,

i had done it earlier, and i got below error

Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.

thanks

Member Avatar for pritaeas

Add a new column, then copy/convert the data to it, then remove the old column and rename the new one.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.