Using % with ILIKE operator
- Dominant language
- C++
- Stars
- 91
- Forks
- 36
- PR merge metrics
- No merged PRs in 30d
Description
Hi,
Just wanted to ask something I'm not fully getting. I'm having trouble on how to correctly use the `ILIKE` operator with the `%` wildcard. I have a table `orders` with a `TEXT` field named `item_id`. This works:
```js
await db.all("SELECT * FROM orders WHERE item_id ILIKE ?;", item)
```
Adding the `%` wildcard in any of the following ways will fail:
```js
await db.all("SELECT * FROM orders WHERE item_id ILIKE %?;", item)
```
```js
await db.all("SELECT * FROM orders WHERE item_id ILIKE ?%;", item)
```
```js
await db.all("SELECT * FROM orders WHERE item_id ILIKE %?%;", item)
```
The error is the same for the 3 examples:
```
[Error: Parser Error: syntax error at or near "%"] {
errno: -1,
code: 'DUCKDB_NODEJS_ERROR',
errorType: 'Parser'
}
```
Adding single quotes for any of the `%` expressions above does not solve the issue.
Another similat issue that I'm getting is when I try to perform a `SELECT` with the `IN` operator. Given an array of ids, e.g: `[1, 2, 3]` How should I correctly write/pass arguments to `db.all`?:
```js
let orders = [1, 2, 3];
await db.all("SELECT * FROM batches WHERE order_id IN (?);", orders);
```
The above snippet produces the following error:
```
[Error: Conversion Error: Could not convert string '1,3,6' to INT64] {
errno: -1,
code: 'DUCKDB_NODEJS_ERROR',
errorType: 'Conversion'
}
```
Is there any documentation on how to write these types of queries?
Thanks in advance,
Lucas.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.