Using TRIM in QUERY Match
up vote
0
down vote
favorite
This is driving me nuts so I beg for assistance!
I have this query
"Select A, B where UPPER(H) matches
'.*(?:^|,|,s)"®EXEXTRACT(Q3,"^[^[{]+")&"(?:,s|,|$).*' limit 1",0)
But I need to change the match so that ignore spaces. Ideally I would just wrap Q3 in a trim but don't think I can do that.
Thanks in advance
sql regex google-sheets
edited Nov 19 at 11:19
Tim Biegeleisen
209k1380129
asked Nov 19 at 11:17
Chris Barrett
827
add a comment |
up vote
0
down vote
favorite
This is driving me nuts so I beg for assistance!
I have this query
"Select A, B where UPPER(H) matches
'.*(?:^|,|,s)"®EXEXTRACT(Q3,"^[^[{]+")&"(?:,s|,|$).*' limit 1",0)
But I need to change the match so that ignore spaces. Ideally I would just wrap Q3 in a trim but don't think I can do that.
Thanks in advance
sql regex google-sheets
edited Nov 19 at 11:19
Tim Biegeleisen
209k1380129
asked Nov 19 at 11:17
Chris Barrett
827
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
This is driving me nuts so I beg for assistance!
I have this query
"Select A, B where UPPER(H) matches
'.*(?:^|,|,s)"®EXEXTRACT(Q3,"^[^[{]+")&"(?:,s|,|$).*' limit 1",0)
But I need to change the match so that ignore spaces. Ideally I would just wrap Q3 in a trim but don't think I can do that.
Thanks in advance
sql regex google-sheets
edited Nov 19 at 11:19
Tim Biegeleisen
209k1380129
asked Nov 19 at 11:17
Chris Barrett
827
This is driving me nuts so I beg for assistance!
I have this query
"Select A, B where UPPER(H) matches
'.*(?:^|,|,s)"®EXEXTRACT(Q3,"^[^[{]+")&"(?:,s|,|$).*' limit 1",0)
But I need to change the match so that ignore spaces. Ideally I would just wrap Q3 in a trim but don't think I can do that.
Thanks in advance
sql regex google-sheets
sql regex google-sheets
edited Nov 19 at 11:19
Tim Biegeleisen
209k1380129
asked Nov 19 at 11:17
Chris Barrett
827
edited Nov 19 at 11:19
Tim Biegeleisen
209k1380129
asked Nov 19 at 11:17
Chris Barrett
827
edited Nov 19 at 11:19
Tim Biegeleisen
209k1380129
edited Nov 19 at 11:19
Tim Biegeleisen
209k1380129
edited Nov 19 at 11:19
Tim Biegeleisen
209k1380129
209k1380129
asked Nov 19 at 11:17
Chris Barrett
827
asked Nov 19 at 11:17
Chris Barrett
827
asked Nov 19 at 11:17
Chris Barrett
827
827
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
A [^[{] pattern matches any char but [ and { and thus also matches whitespace.
You may match any amount of whitespaces at the start, then capture any amount of chars other than [, {, and then match a char other than [, { or whitespace:
=REGEXEXTRACT(B40,"^s*([^[{]*[^[{s])")
Details
^- start of string
s*- 0+ whitespaces
([^[{]*[^[{s])- Group 1:
[^[{]*- 0+ chars other than[and{
[^[{s]- a single char other than[,{and whitespace.
answered Nov 19 at 11:32
Wiktor Stribiżew
301k16122197
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
A [^[{] pattern matches any char but [ and { and thus also matches whitespace.
You may match any amount of whitespaces at the start, then capture any amount of chars other than [, {, and then match a char other than [, { or whitespace:
=REGEXEXTRACT(B40,"^s*([^[{]*[^[{s])")
Details
^- start of string
s*- 0+ whitespaces
([^[{]*[^[{s])- Group 1:
[^[{]*- 0+ chars other than[and{
[^[{s]- a single char other than[,{and whitespace.
answered Nov 19 at 11:32
Wiktor Stribiżew
301k16122197
add a comment |
up vote
0
down vote
A [^[{] pattern matches any char but [ and { and thus also matches whitespace.
You may match any amount of whitespaces at the start, then capture any amount of chars other than [, {, and then match a char other than [, { or whitespace:
=REGEXEXTRACT(B40,"^s*([^[{]*[^[{s])")
Details
^- start of string
s*- 0+ whitespaces
([^[{]*[^[{s])- Group 1:
[^[{]*- 0+ chars other than[and{
[^[{s]- a single char other than[,{and whitespace.
answered Nov 19 at 11:32
Wiktor Stribiżew
301k16122197
add a comment |
up vote
0
down vote
up vote
0
down vote
A [^[{] pattern matches any char but [ and { and thus also matches whitespace.
You may match any amount of whitespaces at the start, then capture any amount of chars other than [, {, and then match a char other than [, { or whitespace:
=REGEXEXTRACT(B40,"^s*([^[{]*[^[{s])")
Details
^- start of string
s*- 0+ whitespaces
([^[{]*[^[{s])- Group 1:
[^[{]*- 0+ chars other than[and{
[^[{s]- a single char other than[,{and whitespace.
answered Nov 19 at 11:32
Wiktor Stribiżew
301k16122197
A [^[{] pattern matches any char but [ and { and thus also matches whitespace.
You may match any amount of whitespaces at the start, then capture any amount of chars other than [, {, and then match a char other than [, { or whitespace:
=REGEXEXTRACT(B40,"^s*([^[{]*[^[{s])")
Details
^- start of string
s*- 0+ whitespaces
([^[{]*[^[{s])- Group 1:
[^[{]*- 0+ chars other than[and{
[^[{s]- a single char other than[,{and whitespace.
answered Nov 19 at 11:32
Wiktor Stribiżew
301k16122197
answered Nov 19 at 11:32
Wiktor Stribiżew
301k16122197
answered Nov 19 at 11:32
Wiktor Stribiżew
301k16122197
answered Nov 19 at 11:32
Wiktor Stribiżew
301k16122197
301k16122197
add a comment |
add a comment |
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%2f53373492%2fusing-trim-in-query-match%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
