datasize feild value question - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

datasize feild value question

Author

Topic

nbritton
Starting Member

22 Posts

Posted - 2011-05-18 : 12:30:30

I have a feild that is imported from a backup application for reporting needs. It stores the feild in ###MB or ###KB or ###GB, how can i convert this to the same metric durring import or to another feild after import. I am thinking it would make sence to send it all to KB.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-18 : 13:09:08

How are you importing and what daoes the field look like?
If it's a string with the two characters at the end and you use sql then

case when right(fld,2) = 'MB' then replace(fld,'MB','')*1000
when right(fld,2) = 'KB' then replace(fld,'KB','')
when right(fld,2) = 'GB' then replace(fld,'GB','')*1000000
end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-18 : 13:39:25

case when right(fld,2) = 'MB' then replace(fld,'MB','')*1024
when right(fld,2) = 'KB' then replace(fld,'KB','')
when right(fld,2) = 'GB' then replace(fld,'GB','')* power(1024,2)
end

Jim

Everyday I learn something that somebody else already knew

nbritton
Starting Member

22 Posts

Posted - 2011-05-24 : 12:40:47

Thanks Guys, i will give that a shot. The string looks like 45.5MB or 256KB.

nbritton
Starting Member

22 Posts

Posted - 2011-05-24 : 12:44:18

I assume the fld should be replaced by the acutal feild name, but what about the right?

nbritton
Starting Member

22 Posts

Posted - 2011-05-24 : 14:02:50

Where did i go wrong?? Here is my code. I am trying to edit the data allready in the db?

select *
from NV_JOB_HISTORY_COPY
where "Transfer Size" like '%KB' AND "Job Id" = '4063' AND Instance = '1067'
case when right("Transfer Size",2) = 'MB' then replace("Transfer Size",'MB','')*1024
when right("Transfer Size",2) = 'KB' then replace("Transfer Size",'KB','')
when right("Transfer Size",2) = 'GB' then replace("Transfer Size",'GB','')* power(1024,2)
end

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 16:22:44

select *,
case when right("Transfer Size",2) = 'MB' then replace("Transfer Size",'MB','')*1024
when right("Transfer Size",2) = 'KB' then replace("Transfer Size",'KB','')
when right("Transfer Size",2) = 'GB' then replace("Transfer Size",'GB','')* power(1024,2)
from NV_JOB_HISTORY_COPY
where "Transfer Size" like '%KB' AND "Job Id" = '4063' AND Instance = '1067'

Note - the where clause probably means only the KB evaluation will get used

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nbritton
Starting Member

22 Posts

Posted - 2011-05-27 : 10:00:38

Yes rigelrivett you are correct. I was testing with single records. I have found that i will need to write this data to a new column. Dose anyone know how i can do that. I have created a new column as an int.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-27 : 10:11:01

something like

update tbl
set newcol =
case when right("Transfer Size",2) = 'MB' then replace("Transfer Size",'MB','')*1024
when right("Transfer Size",2) = 'KB' then replace("Transfer Size",'KB','')
when right("Transfer Size",2) = 'GB' then replace("Transfer Size",'GB','')* power(1024,2)
from NV_JOB_HISTORY_COPY

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources