Qlik does not recognize ' (single quote) before a [ (square bracket) for script running a big query

[GauravG]

Hello,

I have a connection to Big Query and in my app I have a select statement querying the big query table directly and then getting the result set into a qlik table. The query has some string manipulation and regex operations and hence has a lot of single quotation marks for the regex. My issue is with the regexp_replace function using which I replace certain patterns with a '' i.e. empty space. Qlik does not interpret the ' preceding [ which is a part of the regex as the starting single quote and hence messes up the starting and ending quotes.  The ending single quote is marked green and all text after that is green. As a result it does not parse the variable vRunDate as expected and I get an error.

Query :

Error :

I don't know how to get around this issue. I need the loop in Qlik, coz dates would be ad-hoc. I also need the regex parsing to be done in BigQuery because doing that in BQ reduces records from many thousands to a few hundred, thus reducing data load in the qlik qvd. 

Can anyone please suggest me a way forward.
Reference for regexp_replace in BigQuery : https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_replace

Thanks

[GauravG · Accepted solution]

issue resolved. I forgot to add $ sign for vRunDate variable.