selecting only Float variables in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

selecting only Float variables in a table

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-12-06 : 14:09:24

Hello
i have table with 50 variables and 30 of these variables are type: float. i want to round all the float variables to round(Var, 2). is there a way to select only float variables and round them. i do not want to write a long update statement by typing all the 30 variables and rounding them.

Is there a easier way??

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-06 : 14:40:08

Define easier. If it's a one-time update, a copy and paste operation for the 30 columns is fastest and easiest.
Otherwise, you can write a dynamic SQL statement that fetches all FLOAT columns from INFORMATION_SCHEMA and the formulate an update statement.
Takes a lot longer time.

N 56°04'39.26"
E 12°55'05.63"

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-12-06 : 14:53:03

thanks for your reply.
I would have to run this rounding update once every month so i thought it would be easier to do the latter u have mentioned. i could select the float variables from the table i want but how do i dynamically round it?

use database
select column_name from information_schema.columns
where table_name in (select name from sysobjects where xtype='U' and name = 'LAB') and DATA_TYPE = 'float'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-06 : 15:38:18

I'm confused. What's a variable (Can't be a RelVar)? Is that a VALUE in a COLUMN or are talking about changing the datatype of a COLUMN?

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-12-06 : 15:51:11

@lamprey
i am not trying to change the data type of a column
i want to round all the columns of data type float in a table to 2 decimal places. there are 30 columns in my table of type float.

thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-06 : 16:02:32

SELECT 'UPDATE LAB SET '
UNION ALL
SELECT quotename(column_name) + '=ROUND(' + quotename(column_name) + ',2), '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='LAB' AND DATA_TYPE='FLOAT'

That will generate the UPDATE statement for all float columns in the LAB table. You'll have an extra trailing comma on the last column that needs to be removed.

Keep in mind that float is an approximate datatype, and rounding it may not force 2 decimal places.

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-12-07 : 09:15:47

thanks robvolk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources