Is there a way to replace empty string into NULL value in MariaDB?

Question

here is a simplified version of my question:

csv file:

| id | colA | colB |
| 1  |      |  1.5 |
| 2  |      |  2.2 |
| 3  |  3.3 |  3.5 |

...

I am trying to perform a "load data local infile" operation, but I keep getting warnings on "colA"'s first two entries: because they are empty strings for DB when the DB read the file.

Is there a way I can replace them during the load data step?

Thank you so much!

Barmar · Accepted Answer · 2020-12-03 01:46:27Z

2

Use a user variable to transform the column

LOAD DATA LOCAL INFILE "filename"
INTO TABLE tablename
(id, @colA, @colB)
SET colA = NULLIF(@colA, ''), colB = NULLIF(@colB, '')

edited Dec 3, 2020 at 1:46

answered Dec 3, 2020 at 1:13

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

windsound Over a year ago

Also, if you need to do that in multiple lines, do you need "," to separate them out?

windsound Over a year ago

For example, SET colA = NULLIF(@colA, ''), colB = NULLIF(@colB, '') Is that right syntax?

Collectives™ on Stack Overflow

Is there a way to replace empty string into NULL value in MariaDB?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related