How do I use SQL LIKE '%value%' operator with Azure Functions SQL Binding?-CodePudding

With the Preview version of the Azure Function SQL Input Binding, you can easily execute SQL queries by specifying it in the binding, e.g.:

 [FunctionName("GetToDoItem")]
 public static IActionResult Run(
   [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "gettodoitem")]
   HttpRequest req,
   [Sql("select [Id], [order], [title], [url], [completed] from dbo.ToDo where Id = @Id",
     CommandType = System.Data.CommandType.Text,
     Parameters = "@Id={Query.id}",
     ConnectionStringSetting = "SqlConnectionString")]
     IEnumerable<ToDoItem> toDoItem)
        {
            return new OkObjectResult(toDoItem.FirstOrDefault());
        }
    ...

As soon as I try to use the LIKE Operator like e.g.:

 [FunctionName("GetToDoItem")]
 public static IActionResult Run(
   [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "gettodoitem")]
   HttpRequest req,
   [Sql("select [Id], [order], [title], [url], [completed] from dbo.ToDo where Id = @Id and title like '%Stackoverflow%'",
     CommandType = System.Data.CommandType.Text,
     Parameters = "@Id={Query.id}",
     ConnectionStringSetting = "SqlConnectionString")]
     IEnumerable<ToDoItem> toDoItem)
        {
            return new OkObjectResult(toDoItem.FirstOrDefault());
        }
    ...

I'm getting an exception as follows:

Error indexing method 'GetToDoItem' '%Stackoverflow%' does not resolve to a value.

I understand, that this is due to the app setting lookup of the binding expressions, but I can't figure out a way to kind of "escape" these percentage signs. Currently, my only workaround is to execute the query manually.

Is there any other option to use the percentage sign for SQL like comparisons? I've tried a double %% sign as well as escaping the % with a backslash.

CodePudding user response：

This is a workaround solution.

If it turns out that there is a syntax to escape percent characters then this will be rendered immediately obsolete.

But one way of avoiding the issue will be to change the query as below

SELECT [Id],
       [order],
       [title],
       [url],
       [completed]
FROM   dbo.ToDo
WHERE  Id = @Id
       AND title LIKE Concat(Char(37), 'Stackoverflow', Char(37))

This should get constant folded to

title like '%Stackoverflow%'

without including any literal % in the actual query string.