October 27, 2016 at 9:07 am
My first time seeing this:
My table has a column of varchar which contains 9 characters registration numbers, if I run a select query, I get the field showing as scientific notation like 1.2345E+008
After some googlings, it turns out all my findings are using cast or convert, that is not what I want. I believe this should be a common issue (although my first time seeing it) and there should be a way to fix it.
Can anyone shed me more light?
Thank you very much.
October 27, 2016 at 9:44 am
found a simple solution: select str(column)
PS. the above query is producing wrong result, changing 1234565789 to 123456000, I'm keep searching for a simple solution now
October 27, 2016 at 10:33 am
It turns out to be something with the original data import, the function str is fine, and no need because the re-import fixed the issue.
Sorry for confusing.
October 27, 2016 at 10:36 am
It might help if you shared the actual query and perhaps what you're using to view the query output. Scientific notation seems to imply that some part of this process thinks this is numeric content, not a string.
Also - str() is used to convert numeric data to character data: if your data is ALREADY character data str is unnecessary at best, deleterious in most cases. If anything, it will force an implicit conversation to float first then rendering as string, resulting in what you're seeing now.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 27, 2016 at 10:36 am
Are you sure your column is a varchar column?
Are you trying to do some type of mathematical operation on it?
Are you querying using SSMS or something else?
October 27, 2016 at 10:50 am
halifaxdal (10/27/2016)
It turns out to be something with the original data import, the function str is fine, and no need because the re-import fixed the issue.Sorry for confusing.
The STR() function might not be "fine". It's a whole lot slower than other methods and, depending on the scale, could produce rounded answers because it has a FLOAT backbone. Please see the following article.
[font="Arial Black"]http://www.sqlservercentral.com/articles/T-SQL/71565/[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2016 at 1:06 pm
As I said already, it was an error introduced at the beginning of importing data, after I found the cause and re-imported the data, the issue doesn't apply anymore.
But thank you Jeff for the link and others for all the replies.
October 27, 2016 at 1:37 pm
halifaxdal (10/27/2016)
found a simple solution: select str(column)PS. the above query is producing wrong result, changing 1234565789 to 123456000, I'm keep searching for a simple solution now
That's because once the value was stored as a varchar in scientific notation, the precision was lost. You had only an approximate number which converted to float and back to string by using the STR function.
As you know, the only option was to correct the import.
I just wanted to make sure that the reason behind all this was understood to prevent future problems.
October 27, 2016 at 1:51 pm
Thanks Luis.
October 28, 2016 at 12:54 pm
You do not understand the difference between a string and a numeric in SQL. Registration numbers are called "tag numbers" in data modeling; they are a name of an entity that happens be written with digits and perhaps other punctuation rather than alphabetic characters.
You have tried to convert the string of digits into a numeric. The SQL engine has picked the simplest representation of a numeric value that it could find. In this case it happens to be scientific or exponential notation.
If you would followed forum rules and posted DDL, we would have seen this immediately and I had to guess. This mistake is most commonly made by old COBOL programmers, because COBOL represents everything in character strings.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
October 28, 2016 at 1:43 pm
CELKO (10/28/2016)
You do not understand the difference between a string and a numeric in SQL. Registration numbers are called "tag numbers" in data modeling; they are a name of an entity that happens be written with digits and perhaps other punctuation rather than alphabetic characters.You have tried to convert the string of digits into a numeric. The SQL engine has picked the simplest representation of a numeric value that it could find. In this case it happens to be scientific or exponential notation.
If you would followed forum rules and posted DDL, we would have seen this immediately and I had to guess. This mistake is most commonly made by old COBOL programmers, because COBOL represents everything in character strings.
Actually this mostly commonly caused by loading a spreadsheet into SQL Server, the spreadsheet engine forcing exponential format to be used.
COBOL, COBOL, COBOL, you really should get yourself out of the '80s, it's time to move on!
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".
October 28, 2016 at 7:36 pm
Let's ask the poster; was this data from a spreadsheet or a string of some kind?
COBOL, COBOL, COBOL, you really should get yourself out of the '80s, it's time to move on!
COBOL is to commercial programming as Latin is to all of the Romance languages. Most Americans do not study Latin anymore (or a lot of other foreign languages for that matter), but the influence is there even when you are not aware of it.
When I looked at NoSQL languages, I had to face the fact that SQL has become "the COBOL of databases" because even if it if the new guy not a relational database, developers try to create an SQL-like tool to use with it.;-)
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
October 28, 2016 at 8:21 pm
CELKO (10/28/2016)
Let's ask the poster; was this data from a spreadsheet or a string of some kind?COBOL, COBOL, COBOL, you really should get yourself out of the '80s, it's time to move on!
COBOL is to commercial programming as Latin is to all of the Romance languages. Most Americans do not study Latin anymore (or a lot of other foreign languages for that matter), but the influence is there even when you are not aware of it.
When I looked at NoSQL languages, I had to face the fact that SQL has become "the COBOL of databases" because even if it if the new guy not a relational database, developers try to create an SQL-like tool to use with it.;-)
Scott is correct, the original data was imported from excel.
October 31, 2016 at 7:31 am
I doubt the vast majority of developers today have even heard of COBOL, and they certainly have never programmed in it.
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".
October 31, 2016 at 7:46 am
I doubt the vast majority of developers today have even heard of COBOL, and they certainly have never programmed in it.
Yep, and nobody takes Latin either. Yet everywhere you look, in vocabularies, grammars and linguistics, you find the Latin mindset. Would you respect a linguist or writer who has no appreciation for this? Same principle (knowing the heritage and reasons for something in your trade) applies to programming; like it or not, COBOL and FORTRAN are like Latin and Greek for us.;-)
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply