Sql-server – Handling sql column with mixed varchar and float data types

castdatatypessql serversql-server-2012t-sql

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.

Best Answer

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

Related Solutions

Mysql – the difference between MySQL VARCHAR and TEXT data types

divided linked to some info that explains the basic issue (there's performance differences), but it's not simple enough to say that one's always better than the other. (otherwise, there'd be no reason to have both.) Also, in MyISM, the 64k max size for VARCHAR isn't per field -- it's per record.

Basically, there's 4 ways to store strings in database records:

fixed length
C-style strings (marked with a NULL or similar character at the end of the string)
Pascal style strings (a few bytes to indicate length, then the string)
Pointers (store the string somewhere else)

MyISM uses something similar to #3 for VARCHAR, and a hybrid approach for TEXT where it stores the beginning of the string in the record, then rest of the string somewhere else. InnoDB is similar for VARCHAR, but stores the complete TEXT field outside of the record.

With 1&4, the stuff in the record is always the same length, so it's easier to skip over if you don't need the string, but need stuff after it. Both #2 and #3 aren't too bad for short strings ... #2 has to keep looking for the marker, while #3 can skip ahead ... as the strings get longer, #2 gets worse for this particular use case.

If you actually need to read the string, #4 is slower, as you have to read the record, then read the string which might be stored elsewhere on the disk, depending on just how that database handles it. #1 is always pretty straightforward, and again you run into similar issues where for #2 gets worse the longer the string is, while #3 is a little worse than #2 for very small strings, but better as it gets longer.

Then there's storage requirements ... #1 is always a fixed length, so it might have bloat if most strings aren't the max length. #2 has 1 extra byte; #3 typically has 2 extra bytes if max length = 255, 4 extra bytes if a 64k max. #4 has the pointer length, plus the rules for #3 typically.

For the specific implementations within MySQL 5.1, the docs for MyISM state:

Support for a true VARCHAR type; a VARCHAR column starts with a length stored in one or two bytes.
Tables with VARCHAR columns may have fixed or dynamic row length.
The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.

While for InnoDB :

The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 15 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.
For each non-NULL variable-length field, the record header contains the length of the column in one or two bytes. Two bytes will only be needed if part of the column is stored externally in overflow pages or the maximum length exceeds 255 bytes and the actual length exceeds 127 bytes. For an externally stored column, the two-byte length indicates the length of the internally stored part plus the 20-byte pointer to the externally stored part. The internal part is 768 bytes, so the length is 768+20. The 20-byte pointer stores the true length of the column.

...

as with so many other things when dealing with databases, if you're not sure what's best for your needs, try benchmarking it with similar data & usage, and see how they behave.

Sql-server – Changing a column data type from datetime to float

With no data in the column you can just drop it and add it again.

alter table MyTable drop column MyColumn;

go

alter table MyTable add MyColumn float;

If you have data in your column and it makes sense to convert the values to a float value you can rename the column, add a new column, move the data using convert and then drop the old column.

exec sp_rename 'MyTable.MyColumn', 'Temp_MyColumn', 'COLUMN';

go

alter table MyTable add MyColumn float;

go

update MyTable 
set MyColumn = convert(float, Temp_MyColumn) 
where Temp_MyColumn is not null;

go

alter table MyTable drop column Temp_MyColumn;

Best Answer

Related Solutions

Mysql – the difference between MySQL VARCHAR and TEXT data types

Sql-server – Changing a column data type from datetime to float

Related Question