CODEWITHSUNDEEP
sqlsql-servert-sql
infCAT
infCAT

Reputation: 23

Extact datatype from a column value varchar SQL

I have two tables:

table1{
   id: int,
   desc: varchar(50),
   type: varchar(50)
}

table2{
   id(tableX): varchar(20),
   id2(table1): int,
   value: sql_variant
}

Table1 have this row:

id desc type
1 Name varchar(20)
2 Birthday datetime

I want to insert values into table2 using the type defined on table1.

Something like this:

insert into table1 (id,id2,value) values('test',1,cast('John' as (select type from table1 where id = 1)))

Desired table2:

id id2 Value
000000x 1 john
000000x 2 12/03/1960

I'm looking for the SQL equivalent of this C# code:

Type type = Type.GetType("System.Int32");
object value = 12;
Console.WriteLine(Convert.ChangeType(value, type));

is it possible to do this? is there another way?

Thanks.

Upvotes: 0

Views: 72

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272006

You cannot use the CAST function dynamically. You could wrap the cast for each datatype inside CASE but the return datatype will be the one with highest priority and, for example, "John" cannot be converted to a date.

In theory, you could do this and it should work:

SELECT CASE type
       WHEN 'varchar(20)' THEN CAST(CAST('x' AS varchar(20)) AS sql_variant)
       WHEN 'datetime'    THEN CAST(CAST('x' AS datetime   ) AS sql_variant)
       WHEN ...
END

Here is a little proof of concept.

Upvotes: 2

Related Questions