Regular expression to extract certain columns from SQL "CREATE TABLE" statements

Question

Let's say I have a MySQL dump which creates a lot of tables.

Example:

CREATE TABLE `my_table` (
  `id` bigint(20) NOT NULL,
  `REVTYPE` tinyint(4) DEFAULT NULL
  `some_other_column` varchar(255)
);

What whould be a valid regular expression to find the following:

All lines which start with "CREATE TABLE" and which contains "my_" in the table name
Then extracting the line containing "tinyint"

So the result would look like:

CREATE TABLE `my_table` (
  `REVTYPE` tinyint(4) DEFAULT NULL

Possible duplicate of Learning Regular Expressions

Biffen
– Biffen

2018-11-26 15:52:26 +00:00
Commented Nov 26, 2018 at 15:52 — Biffen
– Biffen, Commented Nov 26, 2018 at 15:52

Mark · Accepted Answer · 2018-11-27 15:39:49Z

1

This regex seems to work:

^((CREATE.*my_.*\n)|(\s+.*tinyint.*\n)|(\s+.*(?!tinyint)\n))


CREATE TABLE `my_table` (
  `id` bigint(20) NOT NULL,
  `id` bigint(22) NOT NULL,
  `REVTYPE` tinyint(4) DEFAULT NULL,
  `id` bigint(20) NOT NULL,
  `REVTYPE` tinyint(5) DEFAULT NULL,
  `some_other_column` varchar(255)
);

becomes (replace with $2$3) :

CREATE TABLE `my_table` (
  `REVTYPE` tinyint(4) DEFAULT NULL,
  `REVTYPE` tinyint(5) DEFAULT NULL,
);

[I assume the OP wants the ); at the end -advise if not true.] . See regex101 link:

edited Nov 27, 2018 at 15:39

answered Nov 26, 2018 at 21:40

Mark

192k32 gold badges560 silver badges580 bronze badges

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

4 Comments

Poul Bak Over a year ago

This: '[^tinyint.*]' creates a character Group not containing the characters, it does not match a Word.

Mark Over a year ago

Thanks Poul, I was lazy at the coffeeshop - I changed it to a negative lookahead. Odd that it worked so well as it was...

Poul Bak Over a year ago

This will also match your_table.

Mark Over a year ago

Fixed that easily enough. What I don't know how to do is how to make the 3rd and 4th groups conditional on finding a matching second group? I.e., stop trying to match (the tinyints) if there is no my_table. So result would be no matches at all. I know there are conditional groups but doubt they are supported in vscode.

Collectives™ on Stack Overflow

Regular expression to extract certain columns from SQL "CREATE TABLE" statements

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related