Explain the BQ results?

[rajatanand]

Can anyone please explain me the output of the below query? I have no where set the placeholder ${vL_DeclDept}.

Where does the BigQuery get the value of this variable? I’m just running this in the BQ editor.

Query:

WITH CTE AS
(
  SELECT 'HumanResource' AS Department
  UNION ALL
  SELECT 'InformationTech'
  UNION ALL
  SELECT 'Finance'
  UNION ALL
  SELECT 'Research&Dev'
)

SELECT REGEXP_REPLACE(Department, r'[^${vL_DeclDept}]', '')
FROM CTE

[kolban]

Not an AI response

I’ll give you my interpretation … the REGEXP_REPLACE is going to take each of your rows, look at the value of the “Department” column, apply the Regular Expression replace and output that.

Looking at the docs for REGEXP_REPLACE, they claim that the RegExp variant is based on “re2”:

Syntax

Next we look at the expression … which is:

[^${vL_DeclDept}]

My interpretation is that this matches any single character contained within the square brackets …
I.e. it matches “$”, “{“, “v”, “L” … etc etc

However, because the expression is:

[^….]

That negates the expression … meaning it will match anything NOT in the following characters

Putting that all together, the expression will replace your string with any characters NOT “$”, “{“,”v”,”L” … etc etc

and that’s what we see in the output … we see the input replaced with characters that exist in “vL_DeclDept”.

Your original question alluded to this being a variable … that is NOT the case … there is NO variable expansion here. Don’t confuse “${….}” for a variable expansion. Instead, thing of these as simply characters in a RegExp expression.

Try play in regex101 … we will see this is not a BQ question but a RegExp question