Drizzle Team•7mo ago

libSQL/SQLite: Issue with CAST and ILIKE

I'm trying to cast number and date to string inside the 'where' statement. Unfortunately I'm getting a SQL syntax error around ILIKE. What's the issue with this code?:

.where(
        or(
          ilike(customers.name, `%${query}%`),
          ilike(customers.email, `%${query}%`),
          sql`CAST(invoices.amount AS TEXT) ILIKE ${`%${query}%`}`,
          sql`CAST(invoices.date AS TEXT) ILIKE ${`%${query}%`}`,
          ilike(invoices.status, `%${query}%`),
        ),
      )

.where(
        or(
          ilike(customers.name, `%${query}%`),
          ilike(customers.email, `%${query}%`),
          sql`CAST(invoices.amount AS TEXT) ILIKE ${`%${query}%`}`,
          sql`CAST(invoices.date AS TEXT) ILIKE ${`%${query}%`}`,
          ilike(invoices.status, `%${query}%`),
        ),
      )

1 Reply

Pramus•7mo ago

Forget it... libSQL/SQLite doesn't have ILIKE, my bad. I'll leave it here just in case someone does the same mistake. Here's the correct code:

      .where(
        or(
          sql`LOWER(customers.name) LIKE ${`%${query.toLowerCase()}%`}`,
          sql`LOWER(customers.email) LIKE ${`%${query.toLowerCase()}%`}`,
          sql`CAST(LOWER(invoices.amount) AS TEXT) LIKE ${`%${query.toLowerCase()}%`}`,
          sql`CAST(LOWER(invoices.date) AS TEXT) LIKE ${`%${query.toLowerCase()}%`}`,
          sql`LOWER(invoices.status) LIKE ${`%${query.toLowerCase()}%`}`,
        ),

      .where(
        or(
          sql`LOWER(customers.name) LIKE ${`%${query.toLowerCase()}%`}`,
          sql`LOWER(customers.email) LIKE ${`%${query.toLowerCase()}%`}`,
          sql`CAST(LOWER(invoices.amount) AS TEXT) LIKE ${`%${query.toLowerCase()}%`}`,
          sql`CAST(LOWER(invoices.date) AS TEXT) LIKE ${`%${query.toLowerCase()}%`}`,
          sql`LOWER(invoices.status) LIKE ${`%${query.toLowerCase()}%`}`,
        ),

Gaming

Programming

libSQL/SQLite: Issue with CAST and ILIKE