CODEWITHSUNDEEP
sql
im so confused
im so confused

Reputation: 2111

Select columns, but casting all columns of given type

I need something that is the logical equivalent of

select cast(* as numeric IF data_type='decimal') from table_name

I didn't know how to do that, so i'm trying to reduce it into two steps:

select * from table_name where data_type!='decimal'

select cast(* as numeric) from table_name where data_type='decimal'

But I realized I don't know how to do that, either. I can find the list of column names that are decimal thanks to SO, but as this is a batched process, i can't split this into two steps

Is what I'm trying to do possible? I just want to get a set of columns, but if they're decimal, cast them as double.

Thanks!

Upvotes: 6

Views: 15282

Answers (2)

Tiny Haitian
Tiny Haitian

Reputation: 479

Or, you could create a function as follows (credit goes to poster listed here):

/****** Object:  UserDefinedFunction [dbo].[udf_getColumnDataType]    Script Date: 8/4/2014 10:59:23 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/***************************************************************************
Code stolen from here:
http://bit.ly/1v26PmM
****************************************************************************/
CREATE FUNCTION [dbo].[udf_getColumnDataType] (@TableName varchar(64), @ColumnName varchar(64) )
RETURNS varchar(64)
AS
BEGIN
DECLARE @DataType VARCHAR(10)
SELECT @DataType = 
    t.Name --'Data type'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE
    c.object_id = OBJECT_ID(@TableName)
    and c.name = @ColumnName
    return @DataType
END

You then can check each column and cast accordingly. Just pass the table name and the column name (as string) and it will return it to you.

Select iif(
    dbo.udf_getColumnDataType('your table name', 'your column name') ='decimal',
    cast(your_column_name as numeric), 
    your_column_name
) as col1
from your_table_name

I was trying to create this on SQL Fiddle, but seems like I can't create functions on their page. It worked for me while testing in SQL Server 2012. Hopefully, it's not a long route for you, but worth posting.

Upvotes: 3

Rahul Tripathi
Rahul Tripathi

Reputation: 172428

You have to explicitly cast each column like this:

SELECT CAST(Col1 AS INT),CAST(Col2 AS INT)..
FROM Table

Upvotes: 4

Related Questions