List:General Discussion« Previous MessageNext Message »
From:hsv Date:August 7 2012 7:36pm
Subject:Re: DECIMAL datatype automatically makes blank become 0
View as plain text  
>>>> 2012/08/04 23:14 -0400, Fred G >>>>
I'm using MySQL 5.2 Workbench, and when I import a csv into MySQL, blank
values become 0 when I define the datatype to be a DECIMAL (in our case
(DECIMAL(12,2)). Since there is the potential for values to be 0, this
poses a problem for us. I initially wanted to make all the DECIMAL values
become VARCHAR(255), because I can still do inequality queries on this
column if it is a VARCHAR, and VARCHAR does not mind blanks. It keeps them
as is. 
<<<<<<<<
MySQL does not do real CSV files, only its version of CSV-ish files. What do you mean
"blank"? Is there really a character there, between separators, or nothing at all?

In any case, I guess you mean NULL in SQL terms. In that case, MySQL insists on seeing
NULL on no escape character, or \N, where escape character is \. If this is LOAD DATA, a
trick that another used is helpful here, making use of letting LOAD DATA store in a user
variable, and using SET to make it either the number or NULL:

LOAD DATA .... (..., @DN, ....) .... SET decField = IF(@DN = '', NULL, @DN);

Thread
DECIMAL datatype automatically makes blank become 0Fred G5 Aug
  • Re: DECIMAL datatype automatically makes blank become 0hsv7 Aug
Re: DECIMAL datatype automatically makes blank become 0Fred G6 Aug
  • RE: DECIMAL datatype automatically makes blank become 0Benjamin Stillman6 Aug
    • RE: DECIMAL datatype automatically makes blank become 0Benjamin Stillman6 Aug
      • Re: DECIMAL datatype automatically makes blank become 0Fred G6 Aug