Extracting text following periods as a separator of a specific line item
I'm trying to extract text that is delimited by periods. I'm a tad stumped after trying for too long and hoping someone can help!
In a nutshell, the following string (single string) is an example of a result of a query from column (e.g. Content).
Example string:
Some random text ........................... True
But really something ....................... Okay
Okay, just another test .................... 2010-04 is a good day
I'm trying to add some statements in the SELECT portion of the query to pull data out of Content in this example. All of the rows in the db have the same content, just with different "values" (True, Okay, 2010...).
Example result:
Col-Random | Col2-Something | Col3-Okay
---------------+-----------------+-------------------------
True | Okay | 2010-04 is a good day
I've tried variants of the following:
SELECT
regexp_extract(SUMMARY, r'/.*Some random text.*/g') as Col-Random
....
FROM `table`
sql regex google-bigquery
asked Nov 14 '18 at 19:17
BenBen
11
add a comment |
I'm trying to extract text that is delimited by periods. I'm a tad stumped after trying for too long and hoping someone can help!
In a nutshell, the following string (single string) is an example of a result of a query from column (e.g. Content).
Example string:
Some random text ........................... True
But really something ....................... Okay
Okay, just another test .................... 2010-04 is a good day
I'm trying to add some statements in the SELECT portion of the query to pull data out of Content in this example. All of the rows in the db have the same content, just with different "values" (True, Okay, 2010...).
Example result:
Col-Random | Col2-Something | Col3-Okay
---------------+-----------------+-------------------------
True | Okay | 2010-04 is a good day
I've tried variants of the following:
SELECT
regexp_extract(SUMMARY, r'/.*Some random text.*/g') as Col-Random
....
FROM `table`
sql regex google-bigquery
asked Nov 14 '18 at 19:17
BenBen
11
1
I don't understand what results you want in each case.
– Gordon Linoff
Nov 14 '18 at 19:18
In js, I can just do <string>.match(/.*Some random text.*/g)[0] for example, and that results to "True". I'm looking to do this all in SQL with BigQuery though, so all in the query using REGEXP_EXTRACT in the SELECT statement. I can't just for the life of me figure out regex that makes it work.
– Ben
Nov 14 '18 at 19:22
Add what you tried to the question. Comments are not meant for that. Note in JS,.match(/.*Some random text.*/g)[0]won't returnTrue
– Wiktor Stribiżew
Nov 14 '18 at 19:32
@Ben - did you see the answer? you can apply it to whatever further needs you have! right?
– Mikhail Berlyant
Nov 14 '18 at 19:54
add a comment |
I'm trying to extract text that is delimited by periods. I'm a tad stumped after trying for too long and hoping someone can help!
In a nutshell, the following string (single string) is an example of a result of a query from column (e.g. Content).
Example string:
Some random text ........................... True
But really something ....................... Okay
Okay, just another test .................... 2010-04 is a good day
I'm trying to add some statements in the SELECT portion of the query to pull data out of Content in this example. All of the rows in the db have the same content, just with different "values" (True, Okay, 2010...).
Example result:
Col-Random | Col2-Something | Col3-Okay
---------------+-----------------+-------------------------
True | Okay | 2010-04 is a good day
I've tried variants of the following:
SELECT
regexp_extract(SUMMARY, r'/.*Some random text.*/g') as Col-Random
....
FROM `table`
sql regex google-bigquery
asked Nov 14 '18 at 19:17
BenBen
11
I'm trying to extract text that is delimited by periods. I'm a tad stumped after trying for too long and hoping someone can help!
In a nutshell, the following string (single string) is an example of a result of a query from column (e.g. Content).
Example string:
Some random text ........................... True
But really something ....................... Okay
Okay, just another test .................... 2010-04 is a good day
I'm trying to add some statements in the SELECT portion of the query to pull data out of Content in this example. All of the rows in the db have the same content, just with different "values" (True, Okay, 2010...).
Example result:
Col-Random | Col2-Something | Col3-Okay
---------------+-----------------+-------------------------
True | Okay | 2010-04 is a good day
I've tried variants of the following:
SELECT
regexp_extract(SUMMARY, r'/.*Some random text.*/g') as Col-Random
....
FROM `table`
sql regex google-bigquery
sql regex google-bigquery
asked Nov 14 '18 at 19:17
BenBen
11
asked Nov 14 '18 at 19:17
BenBen
11
edited Nov 14 '18 at 19:50
Ben
asked Nov 14 '18 at 19:17
BenBen
11
asked Nov 14 '18 at 19:17
BenBen
11
asked Nov 14 '18 at 19:17
BenBen
11
11
1
I don't understand what results you want in each case.
– Gordon Linoff
Nov 14 '18 at 19:18
In js, I can just do <string>.match(/.*Some random text.*/g)[0] for example, and that results to "True". I'm looking to do this all in SQL with BigQuery though, so all in the query using REGEXP_EXTRACT in the SELECT statement. I can't just for the life of me figure out regex that makes it work.
– Ben
Nov 14 '18 at 19:22
Add what you tried to the question. Comments are not meant for that. Note in JS,.match(/.*Some random text.*/g)[0]won't returnTrue
– Wiktor Stribiżew
Nov 14 '18 at 19:32
@Ben - did you see the answer? you can apply it to whatever further needs you have! right?
– Mikhail Berlyant
Nov 14 '18 at 19:54
add a comment |
1
I don't understand what results you want in each case.
– Gordon Linoff
Nov 14 '18 at 19:18
In js, I can just do <string>.match(/.*Some random text.*/g)[0] for example, and that results to "True". I'm looking to do this all in SQL with BigQuery though, so all in the query using REGEXP_EXTRACT in the SELECT statement. I can't just for the life of me figure out regex that makes it work.
– Ben
Nov 14 '18 at 19:22
Add what you tried to the question. Comments are not meant for that. Note in JS,.match(/.*Some random text.*/g)[0]won't returnTrue
– Wiktor Stribiżew
Nov 14 '18 at 19:32
@Ben - did you see the answer? you can apply it to whatever further needs you have! right?
– Mikhail Berlyant
Nov 14 '18 at 19:54
1
1
I don't understand what results you want in each case.
– Gordon Linoff
Nov 14 '18 at 19:18
I don't understand what results you want in each case.
– Gordon Linoff
Nov 14 '18 at 19:18
In js, I can just do <string>.match(/.*Some random text.*/g)[0] for example, and that results to "True". I'm looking to do this all in SQL with BigQuery though, so all in the query using REGEXP_EXTRACT in the SELECT statement. I can't just for the life of me figure out regex that makes it work.
– Ben
Nov 14 '18 at 19:22
In js, I can just do <string>.match(/.*Some random text.*/g)[0] for example, and that results to "True". I'm looking to do this all in SQL with BigQuery though, so all in the query using REGEXP_EXTRACT in the SELECT statement. I can't just for the life of me figure out regex that makes it work.
– Ben
Nov 14 '18 at 19:22
Add what you tried to the question. Comments are not meant for that. Note in JS,
.match(/.*Some random text.*/g)[0] won't return True– Wiktor Stribiżew
Nov 14 '18 at 19:32
Add what you tried to the question. Comments are not meant for that. Note in JS,
.match(/.*Some random text.*/g)[0] won't return True– Wiktor Stribiżew
Nov 14 '18 at 19:32
@Ben - did you see the answer? you can apply it to whatever further needs you have! right?
– Mikhail Berlyant
Nov 14 '18 at 19:54
@Ben - did you see the answer? you can apply it to whatever further needs you have! right?
– Mikhail Berlyant
Nov 14 '18 at 19:54
add a comment |
1 Answer
1
active
oldest
votes
... trying to extract text that is delimited by periods
Below example for BigQuery Standard SQL
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'Some random text ........................... True' line UNION ALL
SELECT 'But really something ....................... Okay' UNION ALL
SELECT 'Okay, just another test .................... 2010-04 is a good day'
)
SELECT
SPLIT(line, REGEXP_EXTRACT(line, r'(.{3}[.]+)'))[SAFE_OFFSET(0)] key,
SPLIT(line, REGEXP_EXTRACT(line, r'(.{3}[.]+)'))[SAFE_OFFSET(1)] value
FROM `project.dataset.table`
with result
Row key value
1 Some random text True
2 But really something Okay
3 Okay, just another test 2010-04 is a good day
Note: above assumes minimum number of 4 periods to qualify as separator
so, if you have your line as Some ... random text ........................... True - it still will be properly processed to
key value
Some ... random text True
answered Nov 14 '18 at 19:33
Mikhail BerlyantMikhail Berlyant
59.5k43671
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
});
}
});
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%2f53307336%2fextracting-text-following-periods-as-a-separator-of-a-specific-line-item%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
... trying to extract text that is delimited by periods
Below example for BigQuery Standard SQL
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'Some random text ........................... True' line UNION ALL
SELECT 'But really something ....................... Okay' UNION ALL
SELECT 'Okay, just another test .................... 2010-04 is a good day'
)
SELECT
SPLIT(line, REGEXP_EXTRACT(line, r'(.{3}[.]+)'))[SAFE_OFFSET(0)] key,
SPLIT(line, REGEXP_EXTRACT(line, r'(.{3}[.]+)'))[SAFE_OFFSET(1)] value
FROM `project.dataset.table`
with result
Row key value
1 Some random text True
2 But really something Okay
3 Okay, just another test 2010-04 is a good day
Note: above assumes minimum number of 4 periods to qualify as separator
so, if you have your line as Some ... random text ........................... True - it still will be properly processed to
key value
Some ... random text True
answered Nov 14 '18 at 19:33
Mikhail BerlyantMikhail Berlyant
59.5k43671
add a comment |
... trying to extract text that is delimited by periods
Below example for BigQuery Standard SQL
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'Some random text ........................... True' line UNION ALL
SELECT 'But really something ....................... Okay' UNION ALL
SELECT 'Okay, just another test .................... 2010-04 is a good day'
)
SELECT
SPLIT(line, REGEXP_EXTRACT(line, r'(.{3}[.]+)'))[SAFE_OFFSET(0)] key,
SPLIT(line, REGEXP_EXTRACT(line, r'(.{3}[.]+)'))[SAFE_OFFSET(1)] value
FROM `project.dataset.table`
with result
Row key value
1 Some random text True
2 But really something Okay
3 Okay, just another test 2010-04 is a good day
Note: above assumes minimum number of 4 periods to qualify as separator
so, if you have your line as Some ... random text ........................... True - it still will be properly processed to
key value
Some ... random text True
answered Nov 14 '18 at 19:33
Mikhail BerlyantMikhail Berlyant
59.5k43671
add a comment |
... trying to extract text that is delimited by periods
Below example for BigQuery Standard SQL
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'Some random text ........................... True' line UNION ALL
SELECT 'But really something ....................... Okay' UNION ALL
SELECT 'Okay, just another test .................... 2010-04 is a good day'
)
SELECT
SPLIT(line, REGEXP_EXTRACT(line, r'(.{3}[.]+)'))[SAFE_OFFSET(0)] key,
SPLIT(line, REGEXP_EXTRACT(line, r'(.{3}[.]+)'))[SAFE_OFFSET(1)] value
FROM `project.dataset.table`
with result
Row key value
1 Some random text True
2 But really something Okay
3 Okay, just another test 2010-04 is a good day
Note: above assumes minimum number of 4 periods to qualify as separator
so, if you have your line as Some ... random text ........................... True - it still will be properly processed to
key value
Some ... random text True
answered Nov 14 '18 at 19:33
Mikhail BerlyantMikhail Berlyant
59.5k43671
... trying to extract text that is delimited by periods
Below example for BigQuery Standard SQL
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'Some random text ........................... True' line UNION ALL
SELECT 'But really something ....................... Okay' UNION ALL
SELECT 'Okay, just another test .................... 2010-04 is a good day'
)
SELECT
SPLIT(line, REGEXP_EXTRACT(line, r'(.{3}[.]+)'))[SAFE_OFFSET(0)] key,
SPLIT(line, REGEXP_EXTRACT(line, r'(.{3}[.]+)'))[SAFE_OFFSET(1)] value
FROM `project.dataset.table`
with result
Row key value
1 Some random text True
2 But really something Okay
3 Okay, just another test 2010-04 is a good day
Note: above assumes minimum number of 4 periods to qualify as separator
so, if you have your line as Some ... random text ........................... True - it still will be properly processed to
key value
Some ... random text True
answered Nov 14 '18 at 19:33
Mikhail BerlyantMikhail Berlyant
59.5k43671
edited Nov 14 '18 at 19:53
answered Nov 14 '18 at 19:33
Mikhail BerlyantMikhail Berlyant
59.5k43671
answered Nov 14 '18 at 19:33
Mikhail BerlyantMikhail Berlyant
59.5k43671
answered Nov 14 '18 at 19:33
Mikhail BerlyantMikhail Berlyant
59.5k43671
59.5k43671
add a comment |
add a comment |
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.
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%2f53307336%2fextracting-text-following-periods-as-a-separator-of-a-specific-line-item%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
I don't understand what results you want in each case.
– Gordon Linoff
Nov 14 '18 at 19:18
In js, I can just do <string>.match(/.*Some random text.*/g)[0] for example, and that results to "True". I'm looking to do this all in SQL with BigQuery though, so all in the query using REGEXP_EXTRACT in the SELECT statement. I can't just for the life of me figure out regex that makes it work.
– Ben
Nov 14 '18 at 19:22
Add what you tried to the question. Comments are not meant for that. Note in JS,
.match(/.*Some random text.*/g)[0]won't returnTrue– Wiktor Stribiżew
Nov 14 '18 at 19:32
@Ben - did you see the answer? you can apply it to whatever further needs you have! right?
– Mikhail Berlyant
Nov 14 '18 at 19:54