[SOLVED] Load data Query with regular expression

Load data Query with regular expression

I need to load data that is coming from a csv file to a particular table. I am loading 5 fields of the csv file into the table. I need to apply regular expression for a particular field value in the csv file. IF it doesnt match i need to reject that record. Is it possible ?

This is my load data query:

LOAD DATA LOCAL INFILE ''/test.csv''
INTO TABLE TEST_TABLE FIELDS
TERMINATED BY '',''
LINES TERMINATED BY ''\n''
    (@FIELD1,@FIELD2,@FIELD3,@FIELD4,@FIELD5)
SET
    FIELD1=STR_TO_DATE(@FIELD1,''%d-%m-%Y''), FIELD2=nullif(@FIELD2,''''),
    FIELD3=nullif(@FIELD3,''''), FIELD4=nullif(@FIELD4,''''),
    FIELD5=nullif(@FIELD5,'''');

If the values that is coming in field4 in csv file is equal to either 200 or 300, i need to consider that record and load other values otherwise i need to reject the record.

Sample file::

1),234232323,STATUS,200,33

2),45454545,STATUS,300,33

3),646546445,STATUS,100,33

here 1st and 2nd record should be considered and 3rd record should be rejected.

Solution

LOAD ...;
DELETE TEST_TABLE WHERE field4 NOT IN (200,300);