[MySQL] Load data from .csv applying regex before insert into table
- by Gabriel L. Oliveira
I know that there is a code to import .csv data into a mysql table, and I'm using this one:
LOAD DATA INFILE "file.csv" INTO TABLE foo FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";
The data inside this .csv are lines like this example:
08/e0/Breast_Cancer_Res_2001_Nov_2_3(1)_55-60.tar.gz Breast Cancer Res. 2001 Nov 2; 3(1):55-60 PMC13900
b0/ac/Breast_Cancer_Res_2001_Nov_9_3(1)_61-65.tar.gz Breast Cancer Res. 2001 Nov 9; 3(1):61-65 PMC13901
I just want the first part (the .tar.gz path), always on the pattern
(letter or number)(letter or number) / (letter or number)(letter or number)/...
and the part starting by 'PMC', always on the pattern
PMC(number...)
where 'number' means a number between 0 to 9
and a letter means a letter between a to z (both upper and lower case)
So, applying the LOAD DATA, and the regex, and inserting the result entries on my sql table, the result table should be:
1 08/e0/Breast_Cancer_Res_2001_Nov_2_3(1)_55-60.tar.gz PMC13900
2 b0/ac/Breast_Cancer_Res_2001_Nov_9_3(1)_61-65.tar.gz PMC13901
What should be the SQL command to do all this?
