# Case sensitivity (/docs/orm/v6/prisma-client/queries/case-sensitivity)

Location: ORM > v6 > Prisma Client > Queries > Case sensitivity

Case sensitivity affects **filtering** and **sorting** of data, and is determined by your [database collation](#database-collation-and-case-sensitivity). Sorting and filtering data yields different results depending on your settings:

| Action          | Case sensitive                               | Case insensitive                             |
| --------------- | -------------------------------------------- | -------------------------------------------- |
| Sort ascending  | `Apple`, `Banana`, `apple pie`, `banana pie` | `Apple`, `apple pie`, `Banana`, `banana pie` |
| Match `"apple"` | `apple`                                      | `Apple`, `apple`                             |

If you use a **relational database connector**, [Prisma Client](/orm/v6/prisma-client/setup-and-configuration/introduction) respects your database collation. Options and recommendations for supporting **case-insensitive** filtering and sorting with Prisma Client depend on your [database provider](#options-for-case-insensitive-filtering).

If you use the MongoDB connector, [Prisma Client](/orm/v6/prisma-client/queries/crud) uses RegEx rules to enable case-insensitive filtering. The connector *does not* use [MongoDB collation](https://www.mongodb.com/docs/manual/reference/collation/).

> **Note**: Follow the progress of [case-insensitive sorting on GitHub](https://github.com/prisma/prisma-client-js/issues/841).

Database collation and case sensitivity [#database-collation-and-case-sensitivity]

> [!NOTE]
> In the context of Prisma Client, the following section refers to relational database connectors only.

Collation specifies how data is **sorted and compared** in a database, which includes casing. Collation is something you choose when you set up a database.

The following example demonstrates how to view the collation of a MySQL database:

```sql no-lines
SELECT @@character_set_database, @@collation_database;
```

```text no-copy
  +--------------------------+----------------------+
  | @@character_set_database | @@collation_database |
  +--------------------------+----------------------+
  | utf8mb4                  | utf8mb4_0900_ai_ci   |
  +--------------------------+----------------------+
```

The example collation, [`utf8mb4_0900_ai_ci`](https://dev.mysql.com/doc/refman/8.0/en/charset-collation-names.html), is:

* Accent-insensitive (`ai`)
* Case-insensitive (`ci`).

This means that `prisMa` will match `prisma`, `PRISMA`, `priSMA`, and so on:

```sql no-lines
SELECT id, email FROM User WHERE email LIKE "%prisMa%"
```

```text no-copy
 +----+-----------------------------------+
 | id | email                             |
 +----+-----------------------------------+
 | 61 | alice@prisma.io                   |
 | 49 | birgitte@prisma.io                |
 +----+-----------------------------------+
```

The same query with Prisma Client:

```ts
const users = await prisma.user.findMany({
  where: {
    email: {
      contains: "prisMa",
    },
  },
  select: {
    id: true,
    name: true,
  },
});
```

Options for case-insensitive filtering [#options-for-case-insensitive-filtering]

The recommended way to support case-insensitive filtering with Prisma Client depends on your underlying provider.

PostgreSQL provider [#postgresql-provider]

PostgreSQL uses [deterministic collation](https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC) by default, which means that filtering is **case-sensitive**. To support case-insensitive filtering, use the `mode: 'insensitive'` property on a per-field basis.

Use the `mode` property on a filter as shown:

```ts highlight=5;normal
const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: "prisma.io",
      mode: "insensitive", // Default value: default
    },
  },
});
```

See also: [Filtering (Case-insensitive filtering)](/orm/v6/prisma-client/queries/filtering-and-sorting#case-insensitive-filtering)

Caveats [#caveats]

* You cannot use case-insensitive filtering with C collation
* [`citext`](https://www.postgresql.org/docs/12/citext.html) columns are always case-insensitive and are not affected by `mode`

Performance [#performance]

If you rely heavily on case-insensitive filtering, consider [creating indexes in the PostgreSQL database](https://www.postgresql.org/docs/current/indexes.html) to improve performance:

* [Create an expression index](https://www.postgresql.org/docs/current/indexes-expressional.html) for Prisma Client queries that use `equals` or `not`
* Use the `pg_trgm` module to [create a trigram-based index](https://www.postgresql.org/docs/12/pgtrgm.html#id-1.11.7.40.7) for Prisma Client queries that use `startsWith`, `endsWith`, `contains` (maps to`LIKE` / `ILIKE` in PostgreSQL)

MySQL provider [#mysql-provider]

MySQL uses **case-insensitive collation** by default. Therefore, filtering with Prisma Client and MySQL is case-insensitive by default.

`mode: 'insensitive'` property is not required and therefore not available in the generated Prisma Client API.

Caveats [#caveats-1]

* You *must* use a case-insensitive (`_ci`) collation in order to support case-insensitive filtering. Prisma Client does no support the `mode` filter property for the MySQL provider.

MongoDB provider [#mongodb-provider]

To support case-insensitive filtering, use the `mode: 'insensitive'` property on a per-field basis:

```ts highlight=5;normal
const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: "prisma.io",
      mode: "insensitive", // Default value: default
    },
  },
});
```

The MongoDB uses a RegEx rule for case-insensitive filtering.

SQLite provider [#sqlite-provider]

By default, text fields created by Prisma Client in SQLite databases do not support case-insensitive filtering. In SQLite, only [case-insensitive comparisons of ASCII characters](https://www.sqlite.org/faq.html#q18) are possible.

To enable limited support (ASCII only) for case-insensitive filtering on a per-column basis, you will need to add `COLLATE NOCASE` when you define a text column.

Adding case-insensitive filtering to a new column. [#adding-case-insensitive-filtering-to-a-new-column]

To add case-insensitive filtering to a new column, you will need to modify the migration file that is created by Prisma Client.

Taking the following Prisma Schema model:

```prisma
model User {
  id    Int    @id
  email String
}
```

and using `prisma migrate dev --create-only` to create the following migration file:

```sql
-- CreateTable
CREATE TABLE "User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL
);
```

You would need to add `COLLATE NOCASE` to the `email` column in order to make case-insensitive filtering possible:

```sql
-- CreateTable
CREATE TABLE "User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL COLLATE NOCASE -- [!code highlight]
);
```

Adding case-insensitive filtering to an existing column. [#adding-case-insensitive-filtering-to-an-existing-column]

Since columns cannot be updated in SQLite, `COLLATE NOCASE` can only be added to an existing column by creating a blank migration file and migrating data to a new table.

Taking the following Prisma Schema model:

```prisma
model User {
  id    Int    @id
  email String
}
```

and using `prisma migrate dev --create-only` to create an empty migration file, you will need to rename the current `User` table and create a new `User` table with `COLLATE NOCASE`.

```sql
-- UpdateTable
ALTER TABLE "User" RENAME TO "User_old";

CREATE TABLE "User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL COLLATE NOCASE
);

INSERT INTO "User" (id, email)
SELECT id, email FROM "User_old";

DROP TABLE "User_old";
```

Microsoft SQL Server provider [#microsoft-sql-server-provider]

Microsoft SQL Server uses **case-insensitive collation** by default. Therefore, filtering with Prisma Client and Microsoft SQL Server is case-insensitive by default.

`mode: 'insensitive'` property is not required and therefore not available in the generated Prisma Client API.

## Related pages

- [`Aggregation, grouping, and summarizing`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/aggregation-grouping-summarizing): Use Prisma Client to aggregate, group by, count, and select distinct.
- [`Computed fields`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/computed-fields): This page explains how to use client extensions to add computed fields to Prisma models.
- [`CRUD`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/crud): How to perform CRUD with Prisma Client.
- [`Custom models`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/custom-models): This page explains how to wrap Prisma Client in custom models
- [`Custom validation`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/custom-validation): This page explains how to add custom validation to Prisma Client