Regular expression to extract certain columns from SQL “CREATE TABLE” statements
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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
regex visual-studio-code
asked Nov 26 '18 at 15:44
Robert StrauchRobert Strauch
3,8431158104
add a comment |
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
regex visual-studio-code
asked Nov 26 '18 at 15:44
Robert StrauchRobert Strauch
3,8431158104
1
Possible duplicate of Learning Regular Expressions
– Biffen
Nov 26 '18 at 15:52
add a comment |
0
0
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
regex visual-studio-code
asked Nov 26 '18 at 15:44
Robert StrauchRobert Strauch
3,8431158104
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
regex visual-studio-code
regex visual-studio-code
asked Nov 26 '18 at 15:44
Robert StrauchRobert Strauch
3,8431158104
asked Nov 26 '18 at 15:44
Robert StrauchRobert Strauch
3,8431158104
asked Nov 26 '18 at 15:44
Robert StrauchRobert Strauch
3,8431158104
asked Nov 26 '18 at 15:44
Robert StrauchRobert Strauch
3,8431158104
asked Nov 26 '18 at 15:44
Robert StrauchRobert Strauch
3,8431158104
3,8431158104
1
Possible duplicate of Learning Regular Expressions
– Biffen
Nov 26 '18 at 15:52
add a comment |
1
Possible duplicate of Learning Regular Expressions
– Biffen
Nov 26 '18 at 15:52
1
1
Possible duplicate of Learning Regular Expressions
– Biffen
Nov 26 '18 at 15:52
Possible duplicate of Learning Regular Expressions
– Biffen
Nov 26 '18 at 15:52
add a comment |
1 Answer
1
active
oldest
votes
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:
answered Nov 26 '18 at 21:40
MarkMark
15.3k34361
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 1:53
This will also matchyour_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
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53484611%2fregular-expression-to-extract-certain-columns-from-sql-create-table-statements%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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:
answered Nov 26 '18 at 21:40
MarkMark
15.3k34361
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 1:53
This will also matchyour_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
add a comment |
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:
answered Nov 26 '18 at 21:40
MarkMark
15.3k34361
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 1:53
This will also matchyour_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
add a comment |
1
1
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:
answered Nov 26 '18 at 21:40
MarkMark
15.3k34361
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:
answered Nov 26 '18 at 21:40
MarkMark
15.3k34361
edited Nov 27 '18 at 15:39
answered Nov 26 '18 at 21:40
MarkMark
15.3k34361
answered Nov 26 '18 at 21:40
MarkMark
15.3k34361
answered Nov 26 '18 at 21:40
MarkMark
15.3k34361
15.3k34361
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 1:53
This will also matchyour_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
add a comment |
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 1:53
This will also matchyour_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
1
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
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 1:53
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 1:53
This will also match
your_table.– Poul Bak
Nov 27 '18 at 15:19
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
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
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53484611%2fregular-expression-to-extract-certain-columns-from-sql-create-table-statements%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Possible duplicate of Learning Regular Expressions
– Biffen
Nov 26 '18 at 15:52