Convert type in string stored to original type

Question

Hi

in my table there is one column named TypeNames  of type varchar(50)

I stored type names in string in this column like int,varchar,decimal,bit etc

SO at time of selection I want to convert this typeNames column values to their original type using sql query

like If I stored in string"int" then after conversion it give me int as type

in c# we can achieve this by doint 

Type.GetType("System.int32")  -> it will return Type object of type int

I want to do same using sql

can you please help me for it?

Answer 1

If you are using SQL Server 2012 use try_convert function.

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

It sounds like you are in for an EAV (Entity-Attribute-Value) design. Sometimes this is right, but be prepared that it can cause severe headache further down the road.

As for your actual problem, I would suggest that you backtrack and look at the sql_variant data type. Right now you are in a blind alley.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

in my table there is one column named TypeNames  of type varchar(50)

I stored type names in string in this column like int,varchar,decimal,bit etc

You'll need to resort to ugly dynamic SQL with this design.  Also, consider that when you query the table, all rows returned in a given column must be of the same type so SQL Server will convert data according to the data type with the highest prededence.  If you have varchar value 'A' and integer value '1', the query will return an error when it attempts to convert 'A' to an integer.

You might take a look at the sql_variant data type instead of using varchar(50):

CREATE TABLE dbo.Foo(
	Bar sql_variant
	);
INSERT INTO dbo.Foo VALUES('ABC');
INSERT INTO dbo.Foo VALUES(123);
SELECT Bar, SQL_VARIANT_PROPERTY(Bar, 'BaseType')
FROM dbo.Foo;

---

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

Answer 4

sql _varient dont support nvarchar(max) .. & some other types

Answer 5

sql _varient dont support nvarchar(max) .. & some other types

That is true.  Perhaps we can offer an alternatives if you provide details on what you are trying to accomplish with this design.

---

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

Answer 6

sure

I have one table

in that there are suppose two column

TypeName  Varchar(Max)  -> stores types in string like "int","decimal","Bit" etc. 

OriginalData  VarBinary(Max)     ->  stores original data of any type convert to varbinary & then store in db

I am doing audit kind of thing

I want to stores data of any type to this column N convert back at time of display

Can you help me?

Answer 7

Create a SQL CLR Function in Visual Studio and implement the Conversion in C# using Type Casting. Deploy this Function in to your SQL Server and call this function in your SQL Query.

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString CAST1(SqlBinary myData)
    {
        return (SqlString)System.Text.Encoding.Default.GetString(myData.Value);
    }
};

Deploy this function in your SQL Server and call this function from your TSQl Query.

DECLARE @myDATA AS VARBINARY(MAX)
SET @myDATA = CAST('123' AS VARBINARY(MAX))
SELECT dbo.CAST1(@myDATA)

Note that I have used SqlString (VarChar) as the return type in my CLR function as it is the only data type to which most of the SQL built-in Data Types (except IMAGE Type) can be converted from. Also you do not need to store the Source Data Type as we are not going to convert this back to the Source Data Type. The VarBinary Data will be converted to VarChar for the purpose of display in your TSQL.

---

Service Delivery & Support Management

Answer 8

Having varchar(MAX) to store a data type seems like a bit of over kill. sysname should do.

You will need to do one of:

IF @type = 'int'
   SELECT convert(int, @OriginalData)
ELSE IF @type = 'nvarchar'
   SELECT convert(nvarchar(8000), @OriginalData)
...

Or

SELECT CASE WHEN TypeName = 'int' THEN convert(int, OriginalData) END AS intcol,
       CASE WHEN TypeName = 'nvarchar' THEN convert(nvarchar(8000), OriginalData) END AS nvarcharcol,
       ...
FROM   tbl

You must have separate columns in either the same or different result sets, as a single column in a single result set must be of the same data type, and you rule out sql_variant.

With both alternatives, you client will need to deal with it. And decimals with it's myriad of combinations of precision and scale will be a challenge.

---

Erland Sommarskog, SQL Server MVP, [e-mail]