| Author |
Topic |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2011-05-09 : 13:02:16
|
| I have a field that is numbers and text combined. I need to pull it into a query for future use. How do I convert it to ensure that I do not get numbers in scientific notation?? |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-09 : 13:18:02
|
| Convert it to what? If it's number and string, then your data type is a character type. Could you provide a sample input that is causing grief? I've run in to this before, but it's usually when importing from Excel or a flat file.JimEveryday I learn something that somebody else already knew |
 |
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2011-05-09 : 13:28:07
|
That is exactly when it is happening, I am trying to export it to excel.....The original number is 246023, when I run the query it looks fine, but when I export it I get 2.46E+02quote:
Originally posted by jcb267
I have a field that is numbers and text combined. I need to pull it into a query for future use. How do I convert it to ensure that I do not get numbers in scientific notation??
|
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2011-05-09 : 13:29:19
|
Sorry this question is not too clear.....what is the best way to look at a field of this type - text??quote:
Originally posted by jcb267
I have a field that is numbers and text combined. I need to pull it into a query for future use. How do I convert it to ensure that I do not get numbers in scientific notation??
|
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-09 : 13:31:55
|
| You could try explicitly converting it to what it already is, i.e., convert(varchar(50),griefField), but I think you'll probably need to format the column in Excel first.JimEveryday I learn something that somebody else already knew |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-09 : 13:32:33
|
| If this is strictly an Excel formatting issue, they're not much you can do on the SQL Server side to fix it. Excel should be using General number format for all cells by default, so the value you posted should display without scientific notation.How are you exporting to Excel? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-09 : 13:33:45
|
quote:
Sorry this question is not too clear.....what is the best way to look at a field of this type - text??
I replied before I read this, what do you mean "look at". If your data type is actually text and you're on SQL 2005 or later, convert it to varchar(n)JimEveryday I learn something that somebody else already knew |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2011-05-09 : 13:35:50
|
By "look at" I just mean to see a number/text field as it appears....quote:
Originally posted by jimf
quote:
Sorry this question is not too clear.....what is the best way to look at a field of this type - text??
I replied before I read this, what do you mean "look at". If your data type is actually text and you're on SQL 2005 or later, convert it to varchar(n)JimEveryday I learn something that somebody else already knew
|
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2011-05-09 : 13:38:19
|
When I export it to excel, I just use the results to file wizard and save it to my desktop.....What happens when fields contain numerals and alphabetic characters upon export - eg 424PHP??quote:
Originally posted by robvolk
If this is strictly an Excel formatting issue, they're not much you can do on the SQL Server side to fix it. Excel should be using General number format for all cells by default, so the value you posted should display without scientific notation.How are you exporting to Excel?
|
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-09 : 14:08:30
|
| Excel makes a guess as to what the field is. So what it comes to first is what it decides it is (I think). I often import a string field from Excel, but half way down Excel decides that the value in it is a number, and you get nothing but blanks in your table for those values. And Excel doesn't always let you change the format, or it decides that 0001 doesn't equal '0001 or some other such irritation. I've had some luck using CSV and then importing/exporting from it.JimEveryday I learn something that somebody else already knew |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-09 : 14:11:45
|
If it detects text that cannot be interpreted as a number or date, it will treat it as text. Otherwise it will try to format it as a number and pick the best display format based on the value. |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2011-05-09 : 14:35:34
|
I think I have gotten around it by changing it in excel as needed, makes automation kind of hard! Thanks jimf and robvolk for your help!quote:
Originally posted by robvolk
If it detects text that cannot be interpreted as a number or date, it will treat it as text. Otherwise it will try to format it as a number and pick the best display format based on the value.
|
|
|
|
|
Column Format