2

I am currently trying to replace some of the plural words like removing "s" from "birds" and replacing it as "bird" in bigquery

but I want them to ignore a few words like "less", "james", "this".

I was able to come up with this which ignores the "less" but still butchers james.

    SELECT REGEXP_REPLACE("James likes to chase birds","([^s])s\\b", "\\1" )

The output I am getting is "Jame like to chase bird" but what I am expecting is "James like to chase bird"

Update: I tried to use negative lookahead, but unfortunately, Bigquery Regex(RE2) doesn't support this.

Kishan Kumar
  • 173
  • 1
  • 13
  • When you say *"words like "less", "james", "this""*, do you mean you want to avoid matching `s` at the end of a word if this last `s` is preceded with `i`, `e` or `s`? Then you need `"([^\\W\\d_ies])s\\b"` – Wiktor Stribiżew Jan 08 '22 at 12:03
  • Thanks for that @WiktorStribiżew, but not really, lets say I have "games" I would only want to strip of 's' to get its singular form "game". I don't mind providing these words to be ignored in the regex if possible, not sure how to do that without negative lookahead – Kishan Kumar Jan 08 '22 at 17:44

1 Answers1

1

You can use an alternation here, a regex with two alternatives. In the first alternative, you can capture all the words that are exceptions, into Group 1, and in the second one, use your regex. The replacement will be both group values concatenated:

(?i)\b(less|james|this)\b|([^s])s\b

Replace with \1\2. See the regex demo. Details:

  • (?i) - a case insensitive modifier
  • \b(less|james|this)\b - Group 1: less, james, or this as a whole word
  • | - or
  • ([^s]) - Group 1: any char other than s (NOTE: if you want to only match a letter other than s, you can use [^\W\d_s])
  • s\b - s at the end of a word.

In your code, use

SELECT REGEXP_REPLACE("James likes to chase birds", r"(?i)\b(less|james|this)\b|([^s])s\b", r"\1\2" )
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • 1
    Yes! This is exactly I was looking for, I didn't know I could use both the capture groups, and only one would be present all the time. Thank you so much! – Kishan Kumar Jan 10 '22 at 18:16