I am using LOAD DATA INTO command to read data from a txt file into my Database. In my input, ı need to take '-' characters as null (not the string null). For example;the input
stack - overflow
have to be
1st column = stack 2nd column = null 3rd column = overflow.
How can I do that??
A simple approach would be to first use LOAD DATA without any conversions, then afterwards run some updates to correct the values that you want changing:
UPDATE yourtable
SET col1 = NULL
WHERE col1 = '-'
The LOAD DATA syntax also allows you to specify transformations to your data:
[SET col_name = expr,...]
The column list can contain either column names or user variables. With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.
An example would be:
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @var1)
SET column2 = CASE WHEN @var1 = '-' THEN NULL ELSE @var1 END;
