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.