REGEX: get Create table queries in sql dump

Question

I have an sql dump of different tables each with different amount of fields, I want to insert a query after each one, so I'm trying to find a regex statment that would retreive:

CREATE TABLE  cms_audit (
aud_id bigint NOT NULL IDENTITY(1,1),
  user_id int DEFAULT NULL,
  client_id int NOT NULL,
  aud_event varchar(500) NOT NULL,
  aud_type varchar(150) NOT NULL,
  aud_string varchar(1000) DEFAULT NULL,
  aud_date datetime DEFAULT NULL
) 
-- --------------------------------------------------------

My regex is CREATE TABLE .*-- (in notepad++ I've checked the box that say's . matches newline) in my head this means get all that starts with "create table" and whatever is after it until you reach "--". However this statement is retrieving the entire file instead of getting each "create table" query separately, what am I missing?.

I have also tried CREATE TABLE (.*|\n)*--.. didn't work.

sergiomse · Accepted Answer · 2015-06-11 04:42:18Z

1

You need to use a regex with any character except --. To achieve this you can do:

CREATE TABLE (?:(?!--).)*

EDIT

The ?! is to make a Negative Lookahead of the string --. Nothing with this string will match this expression.

You can see and test it with this link (it's very well explained and a good tool):

https://regex101.com/r/mR9fD4/1

edited Jun 11, 2015 at 4:42

answered Jun 11, 2015 at 4:33

sergiomse

77512 silver badges20 bronze badges

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

3 Comments

Naguib Ihab Over a year ago

That works! Thanks. Why do I have to use the ?! though? can you explain it to me please?

Naguib Ihab Over a year ago

Thanks. Why wouldn't this work though? CREATE TABLE (?:(?!--).*)

sergiomse Over a year ago

Because * repeats only the last character (in this case anything as you put a .) although you'd put (?!--). So this, match all the string. You should put the * after all the group to say that must match anything (.) expect -- any times (*). I hope it is clearer ;)

Collectives™ on Stack Overflow

REGEX: get Create table queries in sql dump

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related