April 18, 2011 at 5:07 am
Morning,
I am importing the following example of data into a SQL 2000 database -
01|171|Fri|A0277730|.03|1|.0333|JOBS|03/12/10||
01|171|Fri|A0277742|.03|2|.0333|JOBS|03/12/10||
01|193|Mon|A0277843|1.45|1|1.4499|JOBS|06/12/10||
01|193|Mon|A0278032|.03|2|.0333|JOBS|06/12/10||
01|193|Mon|A0278054|.23|3|.2333|JOBS|06/12/10||
01|193|Mon|A0278256|1.48|4|1.4833|JOBS|06/12/10||
When imported the data is being held as .03 and .23 as examples.
How can I get it to either import as 0.03 and 0.23 or write a Select statement to return it in this format. The datatype is curretnly VARCHAR.
Thanks
April 18, 2011 at 5:09 am
Store it as a numeric, or cast to numeric.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2011 at 7:29 am
Thanks for the reply - importing it as numeric changes .03 to 0 and 1.45 to 1.
April 18, 2011 at 7:32 am
Err, only if you specify numeric with a 0 scale.
SELECT cast('1.45' AS Numeric(5,2)) returns 1.45
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2011 at 7:35 am
Sorry Gail - what would I need to change the Scale to then - I have never used Scales.
Thanks
April 18, 2011 at 8:06 am
Numeric(x, y)
x indicates the total number of digits that can be stored
y indicates the number of digits on the right of the decimal place
So Numeric(5,2) has the form 999.99 (2 digits to the right of the ., 5 in total)
What you set the precision and scale to depends on what range of values you expect and what accuracy you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2011 at 8:17 am
Ryan Keast (4/18/2011)
Morning,I am importing the following example of data into a SQL 2000 database -
01|171|Fri|A0277730|.03|1|.0333|JOBS|03/12/10||
01|171|Fri|A0277742|.03|2|.0333|JOBS|03/12/10||
01|193|Mon|A0277843|1.45|1|1.4499|JOBS|06/12/10||
01|193|Mon|A0278032|.03|2|.0333|JOBS|06/12/10||
01|193|Mon|A0278054|.23|3|.2333|JOBS|06/12/10||
01|193|Mon|A0278256|1.48|4|1.4833|JOBS|06/12/10||
When imported the data is being held as .03 and .23 as examples.
How can I get it to either import as 0.03 and 0.23 or write a Select statement to return it in this format. The datatype is curretnly VARCHAR.
Thanks
Import into a staging table. The advantages of doing this vs importing directly into your target table are too numerous to mention, but here are two which would help you with this problem.
1. You can easily verify that you've correctly captured every data element.
2. You can SELECT from the staging table, modifying the output of the SELECT to match the datatypes of the target table columns, before committing yourself to INSERTing the data.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 12, 2011 at 4:33 am
Thank you very much for you all your suggestions
June 7, 2011 at 1:26 pm
On sort of the same front, why is it when you define a field as decimal(6,6), which means that we only want data on the right side of the decimal, is SQL Server returning a leading zero? Here is what I expect to see and what I am getting:
should be: .123456
I get: 0.123456
The issue is that this then returns 8 characters instead of the expected 7. Is there any way to limit the length of the decimal field or am I going to have to use all varchar's in my table definitions? (length in my app is very very important and I can not have sql server deciding what's best at this point)
Thanks for your thoughts!
June 7, 2011 at 2:00 pm
markanderson (6/7/2011)
On sort of the same front, why is it when you define a field as decimal(6,6), which means that we only want data on the right side of the decimal, is SQL Server returning a leading zero? Here is what I expect to see and what I am getting:should be: .123456
I get: 0.123456
The issue is that this then returns 8 characters instead of the expected 7. Is there any way to limit the length of the decimal field or am I going to have to use all varchar's in my table definitions? (length in my app is very very important and I can not have sql server deciding what's best at this point)
Thanks for your thoughts!
The default return format for decimal-only numbers (no integer portion) is to lead with a 0. If you need to cut that off, you need to convert to text somewhere along the way.
And talk to the developer who decided that a leading 0 on a decimal number should break things. Talk to them about picking a profession that doesn't require knowing basic standards used by the whole mathematics community planet-wide, like maybe ditch digging or politics.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 2:05 pm
well... thanks for your thoughts... but there is no need to be insulting.
June 7, 2011 at 2:19 pm
I was trying to be humorous, but apparently I missed. Sorry about that.
If you absolutely have to have a string in a specific format, you'll want to define the format. That means either a custom data type, or formatting and other controls in a layer above the database.
I wouldn't recommend a generic "varchar" data type for something that's meant to represent a number with a specific format. Either store it as a decimal, and have the application deal with the leading 0 issue (easy enough to do in the programming languages I'm familiar with); or store it as a User Defined Data type, based on char/varchar storage, but with rules for only accepting numeric data, allowing for a leading period (or requiring one if you won't have numbers >= 1).
The best solution for this will depend on what you are modeling with the data, and how you are dealing with it in your overall data stream.
For example, if it will be used in calculations, you'll probably be better off storing it as numeric data, and dealing the leading 0 in the application that has a sensitivity to that. (My point about a leading 0 breaking the application is that, if that's the case, it should be coded to catch/prevent such from ever getting into the affected objects/functions/modules/etc. Applications should, under most circumstances, be designed to be fault-tolerant of things that are common, like leading 0s on decimal numbers. You don't necessarily have to code for tollerance of rare edge cases, but something that no human eye would blink at in surprise really should be anticipated in the code and it should tollerate it.)
If it will never be used in calculations, or only rarely, then a string with some rules on it might be appropriate. Numbers stored for lables on reports or other "textual" data might fall into this category, where it's not really a number, it's a name for something. (Credit card "numbers" aren't really numeric data, they're names with numbers instead of letters, if you look at them from a functional point of view. Nobody is ever going to add two credit card numbers together for anything meaningful, after all.)
I'll try to remember never to suggest that you go into politics again. That was unconscionable on my part.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 2:43 pm
Very little sleep and data type issues are making me cranky... sorry as well... my bad.
I think I am going to take your point on the fact that mathmatically 0 at the front is actually correct. In other words, there is no such thing as .1234, it is 0.1234.
June 8, 2011 at 6:40 am
markanderson (6/7/2011)
Very little sleep and data type issues are making me cranky... sorry as well... my bad.I think I am going to take your point on the fact that mathmatically 0 at the front is actually correct. In other words, there is no such thing as .1234, it is 0.1234.
It's not that one is right and the other wrong, it's that both are right. That's the kind of thing where, if you want to enforce one right answer over another right answer, you have to do so in the code.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2011 at 7:57 am
GSquared (6/7/2011)
Credit card "numbers" aren't really numeric data, they're names with numbers instead of letters, if you look at them from a functional point of view. Nobody is ever going to add two credit card numbers together for anything meaningful, after all.
I totally agree with this. Additionally, if you store a credit card number in a numeric data type, any leading zeros will be lost:
create table NumberTest (creditCard numeric(10))
go
insert into NumberTest values (0123456789)
select creditCard from NumberTest
creditCard
---------------------------------------
123456789
In his Stairway to Data series, Joe Celko calls this type of number a Tag Number[/url].
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply