SQL - Cast fields to data types

Hello Experts,

I need to convert fields in a query to particular datatypes.  But there's a lemon twist here ... the data types are part of the actual data.  That's confusing, so let me show you an example of the query results.

Data_Field      |      Data_Type
-------------------------------------------------------------
City                          varchar(25)
State                        varhcar(2)
Zip                           numeric
Date                        datetime

One field has the data, the other field has the expected data type.
So in my query, I need to do something like this.

SELECT CAST(Data_Field as Data_Type) as 'Data_Field'

But SQL doesn't like this.  Can anyone help me with syntax to make this work?

Many thanks,
Moe
LVL 1
mauricerichardAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
lluddenConnect With a Mentor Commented:
You need to use dynamic SQL.

DECLARE @Sql VARCHAR(MAX)
DECLARE @T1 TABLE (Data_field VARCHAR(20), Data_Type VARCHAR(20))
INSERT INTO @T1 SELECT 'City','varchar(25)' UNION SELECT 'State','varchar(2)' UNION SELECT 'Zip','Numeric' UNION SELECT 'Date','datetime'
SELECT @SQL =
  COALESCE(
    @SQL + ',Cast([' + t1.Data_Field + '] AS ' + t1.Data_type + ') AS [' + t1.Data_Field + ']',
    'SELECT Cast([' + t1.Data_Field + '] AS ' + t1.Data_type + ') AS [' + t1.Data_Field + ']'
  )
FROM @T1 T1
SET @Sql = @Sql + ' FROM MyTable'

EXECUTE (@Sql)
--Print @SQL
0
 
CluskittCommented:
SELECT CAST([Date] as datetime) AS 'Date'
You should always avoid naming your fields/tables the same as SQL functions/special words, though
0
 
CluskittCommented:
You can also use MyTableName.MyFieldName nomenclature to avoid these situations. However, this won't avoid other issues. One I've seen recently was some here on EE that had a table with a field named Day. He then had a syntax error trying to perform a DateAdd(day,...,...) because SQL interpreted day as the field and not as the DateAdd argument.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
mauricerichardAuthor Commented:
Hi Cluskitt,

Sorry, this is very hard to explain.  Point taken with the naming conventions, but lets not worry about that for now.  Let me try to re-explain this.

The table has 2 fields: 1) Data_Field  2) Data_Type

Imagine you just created a table called Addresses with two fields, Data_Field & Data_Type

Now imagine you ran this statement for this table:
INSERT INTO Addresses(Data_Field, Data_Type) VALUES('City', 'varchar(25)')
INSERT INTO Addresses(Data_Field, Data_Type) VALUES('State', 'varchar(2)')
INSERT INTO Addresses(Data_Field, Data_Type) VALUES('Zipcode', 'numeric')
INSERT INTO Addresses(Data_Field, Data_Type) VALUES('Date', 'datetime')

You'd now have data that looked like I explained in my question.  But it's just data, the data_Type field isn't a SQL datatype ... it's a string of data.

So what I'd like do to is convert the Data_field to whatever Data_Type we entered for it.  So in the example above, when 'City' loads, I want it to be converted to varchar(25) ... when 'Date' loads up I want to convert it to datetime.

So my first thought was: SELECT CAST(Data_Field as Data_Type) as 'Data_Field'
But I get the message: Type c.Data_Type is not a defined system type.
0
 
CluskittConnect With a Mentor Commented:
I understand now. You have to use dynamic SQL as lludden posted, or some similar variations (like using Quotename functions). The way I handled a similar situation was to create a stored procedure that had @Field and @Type as arguments. You then create your query much like the example posted above (if you use Quotename, it surrounds the string with [])
0
 
mauricerichardAuthor Commented:
Great, thanks for all the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.