January 30, 2020 at 5:11 pm
I have a varchar column that has scientific numbers and I want to fix them and keep it in a varchar column. I am running a select statement that is creating a new table from the source table. Any help would be appreciated.
January 30, 2020 at 5:31 pm
can you provide some sample data of what you are seeing and how you would 'fix' them?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 30, 2020 at 5:33 pm
ok - this might be a stupid question - you want it as a number, but still keep it stored as varchar?
why ?
one of the key things and true DBA will tell you is "choose the correct data type"
MVDBA
January 30, 2020 at 5:43 pm
How are these numbers broken ... what does 'fix' mean?
As Mike says, numbers should be stored in NUMERIC or INT formats. It is the job of the presentation layer to format these numbers for display.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 30, 2020 at 6:18 pm
The original column is a varchar type. I want to convert the scientific number to a number without scientific notation (i.e. 38784939843) and I want the value to remain in a varchar column type.
example of data in the column:
552357722 JUN18
55236
33975-49015C16
30427346131103064ap6
1.00002E+18
1.0004E+11
1.17445E+25
3.61243E+17
9.38357E+27
I am fixing this in another column that is float in type by doing it this way:
Cast(Cast([Payment reference/Check number] as bigint) as varchar(50)) AS check_no
I tried this for the varchar column:
Case When [Vendor invoice] Like '%E+%' Then Cast(Convert(numeric(16,0), Cast([Vendor invoice] as Float)) as varchar(50)) Else [Vendor invoice] END AS Invoice
January 30, 2020 at 6:44 pm
Please explain how to convert the following 'scientific numbers':
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 30, 2020 at 7:29 pm
Phil, those are excluded from conversion...I'm just showing what is in the data. The only ones that would be converted would be the numbers with E+ in it. This also explains why I need to keep the column as varchar. This is also why I am trying to use a case statement with trying to convert the data.
January 30, 2020 at 7:48 pm
you already have the code required - what is the issue with using that code?
apart from a possible precision issue that you should be able to sort by your self I don't see anything wrong with it.
January 30, 2020 at 7:56 pm
Phil, those are excluded from conversion...I'm just showing what is in the data. The only ones that would be converted would be the numbers with E+ in it. This also explains why I need to keep the column as varchar. This is also why I am trying to use a case statement with trying to convert the data.
My concern is that trying to get rid of the exponent ("remove the E part") means you'd have to convert to a numeric or similar data type in general, but the entire reason for scientific notation is to express values that are TERRIBLY INCONVENIENT to express without the E part, in some cases I think you would struggle to get the intermediate value in any datatype that is used in SQL Server.
For instance, the number "20 x 10 ^ 100" (read 20 times (10 raised to the 100th power), well, I just don't know where you would store that in a single value on SQL Server other than floating point, which I'm assuming you'd like to avoid given the requirements. I mean what datatype on SQL server lets you store a number with 100 zeros EXCEPT float?
On the other hand if you aren't limited to SQL Server and are good at math and string picking, you could go with any number of available packages:
https://en.wikipedia.org/wiki/List_of_arbitrary-precision_arithmetic_software
_HOWEVER_ expecting to get the exact digits you want in the resulting character string, well heh can't help you there!
I think we're all interested in the "why's" here, although I have a job that so ridiculous with bad datatypes everywhere, I'm a bit more empathetic here heh heck I have dates in varchars that I can only decode by doing a lookup on ANOTHER table. %^)
edit: I have a guess, did you store invoices in excel without formatting the cell as text? That'd produce a mess similar to what you're describing.
January 30, 2020 at 7:58 pm
Frederico,
The issue is, it doesn't work.
I get this error:
Arithmetic overflow error converting float to data type numeric.
When I try this line below:
Case When [Vendor invoice] Like '%E+%' Then Cast(Convert(numeric(24,0), Cast([Vendor invoice] as float)) as varchar(50)) Else [vendor invoice] END AS Invoice,
January 30, 2020 at 8:00 pm
First - you really need to identify the process that is causing the problem and fix that process so you don't get these invalid entries. Second - some of these numbers exceed your 16 digits and will not convert - it looks like you need to convert to at least 28.
Here is an example:
Declare @testData Table (VendorInvoice varchar(30));
Insert Into @testData (VendorInvoice)
Values ('552357722 JUN18')
, ('55236')
, ('33975-49015C16')
, ('30427346131103064ap6')
, ('1.00002E+18')
, ('1.0004E+11')
, ('1.17445E+25')
, ('3.61243E+17')
, ('9.38357E+27');
Select VendorInvoice = coalesce(cast(cast(try_cast(td.VendorInvoice As float) As numeric(28,0)) As varchar(30)), td.VendorInvoice)
From @testData td;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 30, 2020 at 8:06 pm
Frederico,
The issue is, it doesn't work.
I get this error:
Arithmetic overflow error converting float to data type numeric.
When I try this line below:
Case When [Vendor invoice] Like '%E+%' Then Cast(Convert(numeric(24,0), Cast([Vendor invoice] as float)) as varchar(50)) Else [vendor invoice] END AS Invoice,
then it did work - and you should by your own experience already know that such an error is derived from having a numeric defined with insufficient precision/scale which is precisely what I mentioned on my post
January 30, 2020 at 11:48 pm
looks like I have some data too large to convert to numeric because I am getting this error:
Invoice = coalesce(cast(cast(try_cast([Vendor invoice] As float) As numeric(38,0)) As varchar(50)), [Vendor invoice]),
error:
Arithmetic overflow error converting float to data type numeric.
January 31, 2020 at 12:09 am
time for you do do a bit of data analysis and decide what you want to do with it.
look at the values from the sql below - some of these will be the ones crashing your convert to decimal - and depending on what and how it was added to your db you may decide to scrap them, truncate them or reduce scale.
if object_id('tempdb..#floats') is not null
drop table #floats
create table #floats
(myfloat float
)
insert into #floats
select top 10 try_cast([Vendor invoice] As float) as myfloat
from mytable
where try_cast([Vendor invoice] As float) is not null
order by myfloat
insert into #floats
select top 10 try_cast([Vendor invoice] As float) as myfloat
from mytable
where try_cast([Vendor invoice] As float) is not null
order by myfloat desc
January 31, 2020 at 3:38 am
What you really need to do is get with the people that are providing the data and have them give it to you correctly. Having some number like 9.38357E+27 isn't going to give the the actual correct number... it's going to give you 938357 followed by 22 zeros. Based on the other stuff you have in the column I'm thinking that's not an actual correct value after the conversion.
This is what "X" and the others have been trying to tell you. Even if you get the numbers to convert, they're going to be wrong. You MUST get the people providing the data to provide you with the actual correct data.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply