CODEWITHSUNDEEP
typescriptpostgresqlprisma
Matthieu Raynaud de Fitte
Matthieu Raynaud de Fitte

Reputation: 2469

How can I query a string in a non case sensitive way?

My question is as simple as that : I have a query and I am looking for a match on a string such as :

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

I can use string.toLowerCase() but only on specific use cases

How can I get all of the rows where name can be anything such as Example, ExAMple or example but not any other key such as Exàmplé ?

Upvotes: 11

Views: 15322

Answers (3)

Danila
Danila

Reputation: 18526

Depends on your DB. Quoting from the docs:

PostgreSQL uses deterministic collation by default, which means that filtering is case-sensitive. To support case-insensitive filtering, use 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 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.

See docs for more info about other providers

Upvotes: 31

Alex Vopilovskiy
Alex Vopilovskiy

Reputation: 51

Just want to add a couple use cases for people who land here looking for another use-cases i found useful.

If you want to find a record with field that contains some 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 some fields that are containing 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',
                },
            },
        ],
    },
});

Upvotes: 1

luisbar
luisbar

Reputation: 778

You can try out the following:

prisma.market.findMany({
  where: {
    name: {
      contains: filter
    }
  }
})

And if you want to filter using more than one property, you can try out the following

prisma.market.findMany({
  where: {
    OR: [
      {
        name: {
          contains: filter
        }
      },
      {
        description: {
          contains: filter
        }
      }
    ]
  }
})

Upvotes: 1

Related Questions