duckdb / duckdb/duckdb-node

Using % with ILIKE operator

Open
#134 1 comment 0 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.