Getting specific string with regex

Question

I have a file with some SQL commands, I need to get the data only from the lines that start with CREATE table until the semicolon appears. I'm trying to do this with regular expression, but without success for now, can someone help?

DROP TABLE AAJ_TAB;
**CREATE TABLE AAJ_TAB1 (PROT_NU_ACAO CHARACTER(20) NOT NULL, ACAOTIPO_CD SMALLINT NOT NULL)**;
COMMENT ON TABLE AAJ_TAB1 IS 'some text here.'). MNEMONICO: ACAOJUDIC';
COMMENT ON COLUMN AAJ_TAB1.PROT_NU_ACAO IS 'some text here');

DROP TABLE AAJ_TAB2;
**CREATE TABLE AAJ_TAB2 (PROT_NU_TEST CHARACTER(50) NOT NULL, KEYFIELD SMALLINT NOT NULL)**;
COMMENT ON TABLE AAJ_TAB2 IS 'some text here.'). MNEMONICO: ACAOJUDIC';
COMMENT ON COLUMN AAJ_TAB2.PROT_NU_ACAO IS 'some text here too');

more occurrences..

In this example I need to return all values like:

   CREATE TABLE AAJ_TAB2 (PROT_NU_TEST CHARACTER(50) NOT NULL, KEYFIELD SMALLINT NOT NULL)

Thank you!

Please use parser for this problem. Regexes may backfire for dynamic queries. — user7571182
– user7571182, Commented Jun 10, 2020 at 1:49
In future please consider waiting longer before selecting an answer. Quick selections can discourage other answers, may result in incorrect answers going unchecked by readers and imo is discourteous to those still working on their answers (though some do not agree with me on that). There is no rush. Many askers wait at least a couple of hours, some much longer. Just don't forget to select an answer if at least one was helpful to you. — Cary Swoveland
– Cary Swoveland, Commented Jun 10, 2020 at 2:28

Ankit · Accepted Answer · 2020-06-10 01:54:51Z

1

You can use this in JavaScript:

(CREATE TABLE)(.*)(;)

and if you don't want to include ; at the end use this:

(CREATE TABLE)(.*)(?=;)

answered Jun 10, 2020 at 1:54

Ankit

7121 gold badge7 silver badges15 bronze badges

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

3 Comments

Kleiton Moraes Over a year ago

Thank you @Ankit, it solved my problem! Mandy8055, I didn’t know this package, I’ll study it too, thanks!

Kleiton Moraes Over a year ago

@CarySwoveland I understood what you meant, but in my case the string will always come CREATE, I exported the schema of a database and now I am processing this data to generate a new CREATE Table based on other rules. More specifically, I'm taking a IBM DB2 ddl and converting it to an Impala ddl

Cary Swoveland Over a year ago

Kleiton, OK. I see that I misread the text being matched by the regex. I thought the first semicolon was in the line following 'CREATE' in at least one case, but I see I was wrong. Hence, my comment about . not matching newlines.

Garrigan Stafford · Accepted Answer · 2020-06-10 01:56:03Z

0

If you are just trying to extract the lines from the file that follow the structre then a regex could be used to catch it. Something like

^CREATE.*;$

If you run this on the file to where it tries to match lines, then it should only match lines that begin with CREATE and end with a ';'.

Then you would just use the match function(https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/match) on the string of the file.

If you need to do more complicated capturing that involves counting or matching brackets, you will need something more powerful then regex. Like in the comments someone mentioned https://www.npmjs.com/package/js-sql-parser

answered Jun 10, 2020 at 1:56

Garrigan Stafford

1,4031 gold badge11 silver badges20 bronze badges

3 Comments

Kleiton Moraes Over a year ago

Thank you for the explanation @garrigan-stafford! I will read the material you recommended

Cary Swoveland Over a year ago

Note that in the example, "CREATE" is not at the beginning of a line.

Kleiton Moraes Over a year ago

I will follow your tips in the future @cary-swoveland, In no way did I want to belittle the responses of the other colleagues who responded after I got the result I expected here, in fact I was able to learn new ways to get the result in every answer I got, thanks for the recommendations!

Collectives™ on Stack Overflow

Getting specific string with regex

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related