Search code examples
mysqlsqlmariadbload-data-infile

Can LOAD DATA INFILE accept regex for LINES STARTING BY?

My file format:

-----------------------------------
|  CCAr|Next date |Cred. acct|
|---------------------------------|
|  1143|08/01/2019|123456789| 
-----------------------------------

What I want to load into MariaDB 10.3 is only line with data which is line starting with 1143

Is there a way, MariaDB can load just lines starting with REGEXP '^\\|\\s*[[:digit:]]' ?

I tried using LINES STARTING BY REGEXP '^\\|\\s*[[:digit:]]', but I get following error:

SQL Error [1064] [42000]: (conn:833) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'REGEXP '^\|\s*[[:digit:]]'

P.S.: Please note that I can not use IGNORE x LINES here because my file has got repeated header lines which I am not showing here for simplicity.

Solution

  • I don't believe it is possible to apply a regex or where filter during the LOAD DATA command. But, you may proceed to read in all rows initially, and then use a delete statement afterwards:

    DELETE
FROM yourTable
WHERE NOT CCAr LIKE '1143%';