I posted similar thing previously and not much input.
So I try again.
I have this .xls file that I get with a column that has following values for example:
What I want to do is remove GB and put the value in the db
remove KB, Bytes and ignore them
Remove TB, multiply it by 1000 and insert the value in the db.
Not really how to proceed. In the SSIS Package I am using:
(DT_DECIMAL,2)REPLACE(["Aggregate Total Space"]," TB","")
When I try the same using pure sql
"SELECT (CAST(REPLACE([Aggr_Daily_Growth_Rate],'tb','') AS SMALLINT(8))*1000) AS 'xx'
WHERE Aggr_Daily_Growth_Rate NOT LIKE '%bytes' AND Aggr_Daily_Growth_Rate NOT LIKE '% KB'"
I have no issues.
Should I just first put all values the way they come into the db and then use t-sql and then run convert? or purify the data to begin with?
Any help is welcome
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA