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

user_9556253 0
Viewed 1934

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"
    }
})
sequelize.js express node.js mysql
4 Answers

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.

user_17668310 
// 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() + '%')
        }
    })
user_119459320

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!

user_100788950

Your query is right. There is no problem with your query.

You could also try:

pReadings.user_model.findAll({
    where: {
        firstname: { $eq: 'HARSH' }
    }
})
user_116007660
Related