3

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.
  • Bear in mind that you've probably lost numeric precision with the Excel conversion to scientific notation - meaning that whatever conversion you do SQL side will probably not return the correct [Parcel Number] in many cases. I'd recommend re-importing the source file after you fix the encoding issue with the import. – Peter Vandivier May 9 '16 at 2:54
  • Unfortunately this occurs in the source file as well. Our only option is to convert back to numeric and hope for the best. – Geoff Dawdy May 9 '16 at 16:29
  • Well, sometimes that's how the cookie crumbles. I'd still encourage you to double check that the data is in fact corrupted in the source file. See the following screencaps for an example of a file I'm working with just today. While visually Excel may display scientific notation, it may retain the full numeric string underneath ( at least out to e+12 ). If you're using the SSIS stand-alone uploader defaults for .xls, the behavior of the column encoding is read from the .xls file by default & there's a few tricks to force certain behavior. – Peter Vandivier May 9 '16 at 16:58
4

The output from the CASE can only be of one data type. The first branch is decimal and the second is nvarchar(255).

According to the Data Type Precedence (Transact-SQL) rules in SQL Server the resulting data type for the CASE is decimal.

Add one more cast to nvarchar(255) in the first branch of the CASE.

SELECT
CASE 
    WHEN [Parcel Number 1] like '%E+%'
    THEN CAST(CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL) AS NVARCHAR(255))
    ELSE [Parcel Number 1]
END
FROM XLSimport

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.