What is the best method for searching a string without considering uppercase or lowercase letters?

Question

What is the best method for searching a string without considering uppercase or lowercase letters?

Here's a straightforward question I have: I need to search for a specific string match in a query. The code snippet is shown below:

const test_name = 'ExAmPlE'
const database_resources = await prisma.market.findMany({
    where: {
        name: test_name
    }
})

I am aware that I can use string.toLowerCase() in certain cases, but how can I retrieve all rows where the "name" field matches regardless of case, such as Example, ExAMple, or example, but not including outliers like Exàmplé?

typescript postgresql prisma

Answer 1

Answer №1

Depending on the database you are using, the filtering can be case-sensitive or case-insensitive.

PostgreSQL typically uses a deterministic collation method by default, making filtering case-sensitive. To enable case-insensitive filtering, you can specify the mode: 'insensitive' property on a per-field basis:

const test_name = 'ExAmPlE'
const database_resources = await prisma.market.findMany({
    where: {
        name: {
            equals: test_name,
            mode: 'insensitive'
        }
    }
})

MySQL, on the other hand, uses a case-insensitive collation by default. This means that filtering with Prisma Client and MySQL is already case-insensitive without needing to specify the mode: 'insensitive' property in the generated Prisma Client API.

Refer to the documentation for information about case sensitivity with other providers

Answer 2

Depending on the database you are using, the filtering can be case-sensitive or case-insensitive.

PostgreSQL typically uses a deterministic collation method by default, making filtering case-sensitive. To enable case-insensitive filtering, you can specify the mode: 'insensitive' property on a per-field basis:

const test_name = 'ExAmPlE'
const database_resources = await prisma.market.findMany({
    where: {
        name: {
            equals: test_name,
            mode: 'insensitive'
        }
    }
})

MySQL, on the other hand, uses a case-insensitive collation by default. This means that filtering with Prisma Client and MySQL is already case-insensitive without needing to specify the mode: 'insensitive' property in the generated Prisma Client API.

Refer to the documentation for information about case sensitivity with other providers

Answer 3

Answer №2

Here are some examples you can experiment with:

prisma.market.findMany({
  where: {
    title: {
      includes: filter
    }
  }
})

If you're interested in filtering by multiple attributes, give this a try:

prisma.market.findMany({
  where: {
    OR: [
      {
        title: {
          includes: filter
        }
      },
      {
        author: {
          includes: filter
        }
      }
    ]
  }
})

Answer 4

Here are some examples you can experiment with:

prisma.market.findMany({
  where: {
    title: {
      includes: filter
    }
  }
})

If you're interested in filtering by multiple attributes, give this a try:

prisma.market.findMany({
  where: {
    OR: [
      {
        title: {
          includes: filter
        }
      },
      {
        author: {
          includes: filter
        }
      }
    ]
  }
})

Answer 5

Answer №3

For those seeking additional use cases, here are a couple of scenarios that I have found to be useful.

If you're looking to find a record with a field containing a specific word (case-insensitive):

const search = 'ExAmPlE';
const results = await prisma.market.findMany({
    where: {
        name: {
            contains: search,
            mode: 'insensitive',
        },
    },
});

If you want to find a record with multiple fields that contain a word (case-insensitive):

const search = 'ExAmPlE';
const results = await prisma.market.findMany({
    where: {
        OR: [
            {
                name: {
                    contains: search,
                    mode: 'insensitive',
                },
            },
            {
                otherField: {
                    contains: search,
                    mode: 'insensitive',
                },
            },
        ],
    },
});

Answer 6

For those seeking additional use cases, here are a couple of scenarios that I have found to be useful.

If you're looking to find a record with a field containing a specific word (case-insensitive):

const search = 'ExAmPlE';
const results = await prisma.market.findMany({
    where: {
        name: {
            contains: search,
            mode: 'insensitive',
        },
    },
});

If you want to find a record with multiple fields that contain a word (case-insensitive):

const search = 'ExAmPlE';
const results = await prisma.market.findMany({
    where: {
        OR: [
            {
                name: {
                    contains: search,
                    mode: 'insensitive',
                },
            },
            {
                otherField: {
                    contains: search,
                    mode: 'insensitive',
                },
            },
        ],
    },
});

What is the best method for searching a string without considering uppercase or lowercase letters?

Answer №1

Answer №2

Answer №3

Similar questions

Issue with accessing custom method in subclass in Typescript

Typescript struggles to identify properties that have no business being there

Ensuring the correct type of keys during Object.entries iteration in TypeScript

Encountering a connectivity issue with a Postgres database on Fedora, yet operating smoothly on Windows and Gitpod

Warning: The parameter type in the Node JS project is not compatible with the argument type

Enhance the MUI palette by incorporating TypeScript, allowing for seamless indexing through the palette

When working with Typescript, it is important to correctly identify elements as checkboxes in order to avoid any red underlines when referencing

React.js - Issue with table not being redrawn after data filtering

What is the best way to clear all content from the "textarea" and input fields after submitting?

The error message displayed in app.js is: TypeError - it is not possible to call the 'query' method on null

The reset function in Angular's template-driven form does not seem to be functioning properly when implemented using a component

Unable to fulfill the return type requirement in a typed TypeScript function

In TypeScript, specifying that a type should only extend from an object does not effectively prevent strings from being accepted

How to detect changes in Angular 2 using a separate service

Block-level declarations are commonly used in TypeScript and Asp.net MVC 5

Error message "Uncaught in promise" is being triggered by the calendar function within the Ionic

Prevent duplicate API calls in React with TypeScript

Troubleshooting the order of Javascript execution in bundled TypeScript applications

The issue arises when attempting to call a method from the same service within jsPDF in an Angular environment

Retrieving the Object value in Primeng p-dropdown when there is a change in selection