How to compare case sensitive string data in Sequelize ORM for express.js?

Question

I want to compare some case sensitive string data using sequelize. my string is "HARSH" and in db, it is "harsh" which should not be equal. I'm using where condition to find the data "HARSH" but in the response, I'm getting string data "harsh".

pReadings.user_model.findAll({
    where: {
        firstname: "HARSH"
    }
})

Answer 1

The collation on the column needs to be ..._bin. It is probably ..._ci, meaning "case insensitive". It was either set that way by default or explicitly.

Please provide SHOW CREATE TABLE for assistance in changing it.

Answer 2

// search case insensitive nodejs usnig sequelize


 const sequelize = require('sequelize');
    let search = "testData"; // what ever you right here
    pReadings.user_model.findAll({
        where: {
            firstname: sequelize.where(sequelize.fn('LOWER', sequelize.col('firstname')), 'LIKE', '%' + search.toLowerCase() + '%')
        }
    })

Answer 3

Try using the following,

pReadings.user_model.findAll({
    where: sequelize.where(sequelize.fn('BINARY', sequelize.col('firstname')), 'HARSH')
    // SELECT * FROM your_table WHERE BINARY(firstname) = 'HARSH';
})

For more information, check out Querying - Sequelize, under heading "Where > Basics". Good luck!