Get hands-on experience with 20+ free Google Cloud products and $300 in free credit for new customers.

Google.Cloud.BigQuery.V2 (.NET Client) parameterized query with "LIKE" clause

AMSHub
New Member

Hi, 
I'm trying to write the equivalent of a simple parameterized query with a LIKE clause to be executed via the .NET client Google.Cloud.BigQuery.V2 client.ExecuteQuery functionality.

I can run the query in the BigQuery console with actual text (not parameters)

select Field1, Field2 from FullyQualifiedTable where Field2 like 'something%'

and I can run it using the .NET Client by manually injecting the parameter into the sql string, i.e. NOT parameterized which is not SQL injection safe:

string sql = $"select Field1, Field2 from FullyQualifiedTable where Field2 like '{name}%'"

or I can run a parameterized query with no LIKE clause:

string sql = "select Field1, Field2 from FullyQualifiedTable where Field2 = @name";
var parameters = new List<BigQueryParameter> { new ("name", BigQueryDbType.String, name) };

But as soon as I try to use a BigQueryParameter in conjunction with the "like" clause, it either throws an exception or returns no results, depending on which syntax I attempt.  
ex:

string sql = "select Field1, Field2 from FullyQualifiedTable where Field2 like '@name%'"
var parameters = new List<BigQueryParameter> {
new ("name", BigQueryDbType.String, name)
};

I have tried several permutations:

// THings that didn't work:
// like @name
// like @name%
// like '@name%'
// like @name'%'
// like @name ( with parameter definition containing $"{name}%")
// where CONTAINS_SUBSTR(EmployeeName, @name)

and haven't found anything that works.

Is this a bug or is there some specific syntax I just didn't try that will work? 
A LIKE clause is standard SQL and a super basic use case so I feel like this should be supported.

Thanks in advance.

2 1 1,204
1 REPLY 1