We have some Excel spreadsheets with parcel numbers for properties that should be in a standard varchar format similar to:
[Parcel # 1] [Building Address] 038-60-171-16-000 1750 W 4th St 038-60-238-09-000 2003-2027 W 4th St 01-15393 201 5th St NE 01-13504 101 5th St SE 25-20-00-03-008.501 1800 E 5th St 36-102-0700-000 2932 S 5th St 280615 2600-B 6th St SW 31-0101.000 1202 16th St 054-259620-00.000 843 N 21st St
Unfortunately we have some parcel numbers that were converted to scientific notation. This means they are in the wrong format for Parcel Numbers and should be converted back to regular decimal or numeric format. I am encountering difficulty converting just those values that have the scientific notation. The column is of nvarchar(255) data type but these values need to be manipulated back into numeric or decimal data type.
Parcel Number 1(Min) Building Address 3.4007000221e+015 247 S Burnett Rd 3.1844401004e+013 433 N Cleveland Ave 3.4007000222e+016 2415 Lexington Ave E020-0010-006-0211 7271 N Main St 3.6360705006e+013 825 W Market St 3.4007000353e+015 148 W North St 1.40109139e+011 4453-4461 SR-159 16-B-029-E-00-004 7314-7352 Industrial Park Blvd
The above table was retrieved with the following SELECT statement
SELECT [Parcel Number 1] ,[Building Address] FROM XLSimport WHERE [Parcel Number 1] like '%e%'
My understanding is that I should be able to cast the value to a decimal when it's in scientific notation. However, other parcel numbers that have a legitimate 'E' need to remain the same. How can I account for the different 'e' values and still convert the parcel numbers in scientific notation back to their standard parcel number format?
I frequently run into the error converting nvarchar to numeric error messages and I'm wondering if it's because of the parcel numbers that have an E but are not in scientific format.
For instance I run this query:
SELECT CASE WHEN [Parcel Number 1] like '%E+%' THEN CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL) ELSE [Parcel Number 1] END FROM XLSimport
I then get the following error:
Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to numeric.