why my query returns scientific notation for a varchar field?

Question

why my query returns scientific notation for a varchar field?

halifaxdal

SSCoach

Points: 19741
More actions
October 27, 2016 at 9:07 am

#318470

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.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

halifaxdal SSCoach Points: 19741 More actions · Answer 1

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

halifaxdal SSCoach Points: 19741 More actions · Answer 2

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.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

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?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

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?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

halifaxdal SSCoach Points: 19741 More actions · Answer 6

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

halifaxdal SSCoach Points: 19741 More actions · Answer 8

halifaxdal

SSCoach

Points: 19741

October 27, 2016 at 1:51 pm

#1909231

Thanks Luis.

Joe Celko SSCertifiable Points: 5749 More actions · Answer 9

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

ScottPletcher SSC Guru Points: 101091 More actions · Answer 10

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".

Joe Celko SSCertifiable Points: 5749 More actions · Answer 11

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

halifaxdal SSCoach Points: 19741 More actions · Answer 12

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.

ScottPletcher SSC Guru Points: 101091 More actions · Answer 13

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".

Joe Celko SSCertifiable Points: 5749 More actions · Answer 14

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