How to convert varbinary field [Resolved]

Posted by Jayakumars under Sql Server on 11/11/2016 | Points: 10 | Views : 2446 | Status : [Member] [MVP] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi

I have one column with varbinary datatype
in sql server and i inserted guid in my varbinary data type column
how to reverse value after insert sql server or c# how will get this value.

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Rajnilari2015 on: 11/12/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

0	@ JayaKumar Sir, try this DECLARE @t TABLE(Id INT IDENTITY, ColVarBinaryData VARBINARY(MAX)) INSERT INTO @T Select CAST('Test' AS VARBINARY(MAX)) UNION ALL SELECT CAST('Hello DNF !!!' AS VARBINARY(MAX)) Select * , OriginalString = CAST(ColVarBinaryData AS VARCHAR(MAX)) ,BinaryToString = LOWER(SUBSTRING(MASTER.dbo.Fn_varbintohexstr(ColVarBinaryData), 3, 8000)) From @T Result ------------- Id ColVarBinaryData OriginalString BinaryToString 1 0x54657374 Test 54657374 2 0x48656C6C6F20444E4620212121 Hello DNF !!! 48656c6c6f20444e4620212121 -- Thanks & Regards, RNA Team Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

@ JayaKumar Sir, try this

DECLARE @t TABLE(Id INT IDENTITY, ColVarBinaryData VARBINARY(MAX))

INSERT INTO @T Select CAST('Test' AS VARBINARY(MAX)) UNION ALL SELECT  CAST('Hello DNF !!!' AS VARBINARY(MAX))



Select 

		*

		, OriginalString = CAST(ColVarBinaryData AS VARCHAR(MAX))

		,BinaryToString = LOWER(SUBSTRING(MASTER.dbo.Fn_varbintohexstr(ColVarBinaryData), 3, 8000))

From @T

Result
-------------

Id	ColVarBinaryData	       OriginalString	BinaryToString

1	0x54657374	                Test	        54657374

2	0x48656C6C6F20444E4620212121	Hello DNF !!!	48656c6c6f20444e4620212121

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Manicse on: 11/17/2016 [Member] Bronze | Points: 25

0	The GUID is considering to be a UniqueIdentifier. basically we cant keep this GUID as int because even it is number it was more than range of INT. We can use the following method to convert. DECLARE @program_guid uniqueidentifier, @program_binary binary(16) SELECT @program_guid = '327EBEA7-3D4E-473A-8D72-7CB68AB51F73' SELECT @program_binary = CAST(@program_guid AS binary(16)) Mani.R Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

The GUID is considering to be a UniqueIdentifier. basically we cant keep this GUID as int because even it is number it was more than range of INT.
We can use the following method to convert.

DECLARE @program_guid uniqueidentifier, @program_binary binary(16)



SELECT @program_guid = '327EBEA7-3D4E-473A-8D72-7CB68AB51F73'

SELECT @program_binary = CAST(@program_guid AS binary(16))

Mani.R

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts