Convert VarBinary type to Any Type

Question

Hi 

In my database there in two columns

Type contains types in string like "system.int32","Bool","String" etc

Value contains corrosponding to type column Values in VarBinary format 

Now I want to convert VarBinary type to its original type stored in Type Column at the time of select Query in sql

How to do that?

Answer 1

Hi,

There is no type called System.Int32 in SQL so you cannot convert to that. I believe you would like to convert it in you app. If so, then please check casting and type conversion guideline at here. 

I hope it helps.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 2

Its just example

I will store "int" in Type column

Now in sql side I want to convert varbinary to int type

So is there any  way to do that in sql?

Answer 3

If you want to convert varbinary to in try cast(varbinaryCol as int) or use Convert(int,varbinaryCol)

If you want to use the datatype stored in a table you have to use dynamic SQL, there's no other way:

declare @sql varchar(max)
set @sql = 'select cast(varbinaryCol as ' + typecol + ') from table'

exec (@sql)

Answer 4

Hi,

please take a look on the CAST and CONVERT functions at here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 5

Hi 

In my database there in two columns

Type contains types in string like "system.int32","Bool","String" etc

Value contains corrosponding to type column Values in VarBinary format 

Now I want to convert VarBinary type to its original type stored in Type Column at the time of select Query in sql

How to do that?

This question is very similar to the other question you posted.  The same considerations apply so please take a look at your other thread.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

ya but in that question I also dont get any proper solution 

One person suggest sql_varient but it dont support all types 

Answer 7

HI Vipul !

You may get the desired output using below query;

DECLARE    @Test VARBINARY(12)
SELECT    @Test = 123456789
SELECT    CAST(@Test AS INT) ConvertedBacktoINT, @Test ConvertedToVarbinary

Please let me know if this doesn’t work for you. Hope I have answered you correctly.

Thanks, Hasham Niaz

Answer 8

DECLARE    @Test VARBINARY(12)
SELECT    @Test = 123456789
SELECT    CAST(@Test AS INT) ConvertedBacktoINT, @Test ConvertedToVarbinary

Here You write "INT"

But I store Int as string"int" in TypeName column 

in that column Data can be int, or decimal or bit or varchar anything

How can I write type there in casting function?

If "int" comes in TypeName particular row then I want to get Int type object 

(like we do in c# Type.getType(system.Int32"))

& then using that int type object I want to convert varbinary data to to Int type

How to do that?

Answer 9

The design is something you need to revisit definitely.

However, please check the below one. There is no gurantee on the perfromance side.

Create Table T1 (col1 Varchar(MAX),Col2 VARBINARY(MAX))
Insert into T1 Select 'int',Convert(varbinary(MAX),123123)
Insert into T1 Select 'varchar(50)',Convert(varbinary(MAX),'12')
Insert into T1 Select 'varchar(MAX)',Convert(varbinary(MAX),'Latheesh')
Create Table #Temp (id int identity(1,1),Stmt Varchar(1000))
Insert into #Temp 
Select 'select cast(Col2 as '+Col1+') from (Select Col1,Col2,ROW_NUMBER() Over(Order by (Select NULL))RN
From T1)A
' as Stmt  From T1
Declare @i int = 0
Declare @count int = (Select COUNT(1) From #Temp)
declare @sql varchar(1000)
Set @sql=''
While (@i <= @count)
Begin
	Select @sql=stmt + ' where RN =' + Cast(@i as varchar(100))  From #Temp Where id = @i
	print @sql
	exec (@sql)
	Set @sql = ''
	Set @i = @i +1
End
Drop table #Temp
Drop table T1

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!