CODEWITHSUNDEEP
regexvisual-studio-code
Robert Strauch
Robert Strauch

Reputation: 12906

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

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:

So the result would look like:

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

Upvotes: 0

Views: 549

Answers (1)

Mark
Mark

Reputation: 182441

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:

Upvotes: 1

Related Questions