0

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
Robert Strauch
  • 12,055
  • 24
  • 120
  • 192
  • 1
    Possible duplicate of [Learning Regular Expressions](https://stackoverflow.com/questions/4736/learning-regular-expressions) – Biffen Nov 26 '18 at 15:52

1 Answers1

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:

Mark
  • 143,421
  • 24
  • 428
  • 436
  • 1
    This: '[^tinyint.*]' creates a character Group not containing the characters, it does not match a Word. – Poul Bak Nov 26 '18 at 22:24
  • Thanks Poul, I was lazy at the coffeeshop - I changed it to a negative lookahead. Odd that it worked so well as it was... – Mark Nov 27 '18 at 01:53
  • This will also match `your_table`. – Poul Bak Nov 27 '18 at 15:19
  • 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. – Mark Nov 27 '18 at 15:45