QUERY RegEx and &

Question

I've got a long google sheets QUERY, part of which is this:

=QUERY(LOOKUP!$A$4:$H,"Select count(B) where UPPER(D) matches 'OK' and UPPER(H) matches '.*(?:^|,|,\s)"&REGEXEXTRACT(REGEXREPLACE($Q3,"\s|-","")," \w+ ")&"(?:,\s|,|$).*' and (UPPER(C) contains '"&REGEXEXTRACT($Q3, "\{(\w+)\}")&"' or UPPER(F) contains '"&REGEXEXTRACT($Q3, "\{(\w+)\}")&"') limit 1 label count(B) ''",0)

Basically if I have an entry like apple {pear}, I only want the apple bit to be matched as part of the query. This works absolutely fine except if I put an & in the bit to match eg. apple&banana {pear}the match fails even though apple&pearis definetely present in the lookup so I think the issue is with my RegEx. I've tried just replacing \w+ selector at the seperated spot in in the RegEx with .* above but no luck.

Any help would be much appreciated

It would make more sense to replace `\w+` with `\w+(?:&\w+)*` — Wiktor Stribiżew, Dec 04 '18 at 19:04
Thanks @wiktor. I ended up using `[A-z0-9&]+` which seems to work but if you can see an issue I'll give your suggestion a go. Cheers — Chris Barrett, Dec 05 '18 at 11:52
Oh, yeah, there are issues, or at least one. Do not use `[A-z]`. See [here why](https://stackoverflow.com/questions/29771901/why-is-this-regex-allowing-a-caret/29771926#29771926). I posted an answer with explanations. — Wiktor Stribiżew, Dec 05 '18 at 11:58
See my answer below, if it works for you, please consider accepting/upvoting. — Wiktor Stribiżew, Dec 05 '18 at 19:14

score 0 · Answer 1 · answered Dec 05 '18 at 12:01

It would make more sense to replace \w+ with \w+(?:&\w+)*.

The \w+(?:&\w+)* pattern matches

\w+ - 1 or more word chars, letters, digits or _
(?:&\w+)* - matches 0 or more occurrences of:
- & - a & char
- \w+ - 1 or more word chars, letters, digits or _

If you do not want to match _, use

[A-Za-z0-9]+(?:&[A-Za-z0-9]+)*

Note that [A-Za-z0-9&]+ can also be used if you do not care if there are consectuvie & chars in the input (and want to match it).

QUERY RegEx and &

1 Answers1