Menu
I have a table and the columns on this table contains empty spaces for some records. Now I need to move the data to another table and replace the empty spaces with a NULL value.
I tried to use:
REPLACE(ltrim(rtrim(col1)),' ',NULL) but it doesn't work. It will convert all of the values of col1 to NULL. I just want to convert only those values that have empty spaces to NULL.
312
You need to use NULLIF() function from MySQL. The syntax is as follows: SELECT NULLIF(yourCoumnName,' ') as anyVariableName from yourTableName; In the above syntax, if you compare empty string( ' ') to empty string( ' '), the result will always be NULL.
In particular, null values must be distinguished from blank values: A null database field means that there is no value for a given record. It indicates the absence of a value. A blank database field means that there is a value for a given record, and this value is empty (for a string value) or 0 (for a numeric value).
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces, spaces are considered as values because they are strings (and sql can't tell what the value of a string means to the user per se), but a NULL signifies a missing value, and hence has no value associated ...
I solved a similar problem using NULLIF function:
UPDATE table SET col1 = NULLIF(col1, '') From the T-SQL reference:
NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.
102
Did you try this?
UPDATE table SET col1 = NULL WHERE col1 = '' As the commenters point out, you don't have to do ltrim() or rtrim(), and NULL columns will not match ''.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
