November 8, 2012 at 11:37 am
Hello Everyone
I am pumping data in to tables from different text file sources. Some of the data is much closer to correct, and some is way off from normal.
I see some data as: 480.90 or 480.91 from the better source
And I see some data as this: 48090 or 48091 from the not so good source
All of the data from all sources is being places into the same staging table, no big deal for this. But I need to modify this data in the cleansing table.
I need this data to be stored in a decimal(3,2) column. How can I modify the not so good data to become 480.91?
All of the data will be presented as either 4 or 5 digits. Either way, I am looking to store the data as 480.91 or 480.90 so that the values can be converted and stored as a decimal.
Thanks in advance for all your assistance and help
Andrew SQLDBA
November 8, 2012 at 11:41 am
Update to Original post
I just saw some of the data as only 3 digits. So 499 would need to be converted to 499.00 etc.....
There may also be a single digit, I am almost certain there will be.
1 would need to become 100.00
Thanks again for all your help and assistance
Andrew SQLDBA
November 8, 2012 at 12:52 pm
So you have already loaded this data into a table and some of the rows contain decimal places and other do not? And you want to add the decimal place when there isn't one? What is the datatype of the column that is holding these values?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2012 at 1:23 pm
IF the column in varchar you could do something like this.
;with data (SomeVal) as
(
select '480.90' union all
select '480.91' union all
select '48090' union all
select '48091'
)
select case charindex('.', SomeVal, 0) when 0 then stuff(SomeVal, len(SomeVal) - 1, 0, '.') else SomeVal end as SomeNewVal
from data
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2012 at 1:53 pm
AndrewSQLDBA (11/8/2012)
I just saw some of the data as only 3 digits. So 499 would need to be converted to 499.00 etc.....
This particular component is going to take this from relatively simple to nearly impossible to do in bulk without some kind of source identifier. You're most likely going to have to create an import process for each of the sources. Are you using SSIS for the import work or BCP? If SSIS, use a derived column for the source data flow and adjust the values there before bringing it to staging.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2012 at 3:05 pm
This might help you, considering your second post. But you won't be able to use a decimal(3,2), I'm assuming you meant decimal(5,2) that will support values like 499.99
;with data (SomeVal) as
(
select '480.9' union all
select '480.91' union all
select '48090' union all
select '48091' union all
select '399' union all
select '1' union all
select ''
)
select CAST( LEFT(
CASE CHARINDEX('.', SomeVal, 0)
WHEN 0 THEN STUFF(SomeVal + '000000', 4, 0, '.')
ELSE SomeVal + '00' END
, 6) AS decimal(5,2)) as SomeNewVal
from data
November 8, 2012 at 5:07 pm
AndrewSQLDBA (11/8/2012)
Update to Original postI just saw some of the data as only 3 digits. So 499 would need to be converted to 499.00 etc.....
There may also be a single digit, I am almost certain there will be.
1 would need to become 100.00
Thanks again for all your help and assistance
Andrew SQLDBA
IF you've always got at least 3 digits, seems pretty straightfoward:
SELECT
--CAST( --shouldn't be needed, as SQL will automatically cast to recving column type
LEFT(REPLACE(column_name, '.', '') + '00000', 5)
--AS decimal(5, 2)) --shouldn't be needed, as SQL will automatically cast to recving column type
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 9, 2012 at 2:26 am
AndrewSQLDBA (11/8/2012)
1 would need to become 100.00
HOw can this be possible or yuor are trying to say 1.00
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 9, 2012 at 8:44 am
ScottPletcher (11/8/2012)
AndrewSQLDBA (11/8/2012)
Update to Original postI just saw some of the data as only 3 digits. So 499 would need to be converted to 499.00 etc.....
There may also be a single digit, I am almost certain there will be.
1 would need to become 100.00
Thanks again for all your help and assistance
Andrew SQLDBA
IF you've always got at least 3 digits, seems pretty straightfoward:
SELECT
--CAST( --shouldn't be needed, as SQL will automatically cast to recving column type
LEFT(REPLACE(column_name, '.', '') + '00', 5) * 0.01
--AS decimal(5, 2)) --shouldn't be needed, as SQL will automatically cast to recving column type
CORRECTION: D'OH, forgot the * 0.01.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 9, 2012 at 9:46 am
Scott, your code is giving me an error, but I corrected it by using only string functions.
STUFF(LEFT(REPLACE(SomeVal, '.', '') + '000000', 5), 4, 0, '.')
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply