BigQuery raw string support has regressed

[pmsanford]

This issue is present on the current release and on main.

In the following:

>>> q = "SELECT r'\s'"
>>> print(sqlglot.parse_one(q, "bigquery").sql("bigquery"))
SELECT '\s'

SELECT r'\s' is not the same as SELECT '\s' - the r prefix indicates the following is a "raw string," mostly used in REGEXP_REPLACE/REGEXP_EXTRACT (the docs say it is also referred to as a "regex string")

It looks like this was addressed in #218, but it appears to have regressed in d2377e0

The test here in test_bigquery.py is now incorrect:

        self.validate_all(
            r'R"""/\*.*\*/"""',
            write={
                "bigquery": r"'/\*.*\*/'",
                "duckdb": r"'/\*.*\*/'",
                "presto": r"'/\*.*\*/'",
                "hive": r"'/\*.*\*/'",
                "spark": r"'/\*.*\*/'",
            },
        )

The query string R"""/\*.*\*/""" starts with R", so if the leading R is removed, all the backslashes in the resulting strings should be double escaped.

I'm working on wrapping my head around the tokenizer to see if I can fix this, but I thought I'd post an issue in the interim in case I'm called away from working on this before I can get to a PR.

Fully reproducible code snippet
assert sqlglot.parse_one(r"SELECT r'\s'", "bigquery").sql("bigquery") == r"SELECT '\\s'"

Official Documentation
https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#quoted_literals

[georgesittas]

I can take a look shortly, thanks for the report.

[pmsanford]

Here's a possible fix:
pmsanford@653d6ab

It feels a little clunky to me though

[tobymao]

i'm working on something @pmsanford appreciate the attempt