5

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"
    }
})
vrintle
  • 5,501
  • 2
  • 16
  • 46
Harsh Lodhi
  • 159
  • 4
  • 11

4 Answers4

3

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
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() + '%')
        }
    })
Ajay Prajapati
  • 174
  • 4
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-ask). – Community Sep 13 '21 at 11:29
1

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!

vrintle
  • 5,501
  • 2
  • 16
  • 46
0

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

You could also try:

pReadings.user_model.findAll({
    where: {
        firstname: { $eq: 'HARSH' }
    }
})
vrintle
  • 5,501
  • 2
  • 16
  • 46
Vinit Pradhan
  • 11
  • 2
  • 1
    There is a problem, i.e., `$eq` is case-insensitive query. So, it'll match "Harsh", "harsh", etc. too. [See here for an explanation](https://stackoverflow.com/questions/20695062/sequelize-or-condition-object/31390257#31390257) – vrintle Apr 15 '20 at 18:24
  • 1
    $eq is not case-insensitive – Vinit Pradhan Apr 16 '20 at 12:14
  • Do you have any reference? I thought that SQL and sequelize are analogous in terms of their syntax, so I said that `$eq` is case-insensitive. Plus, have you read the answer carefully, that I linked previously? In that, section "Will generate" shows that analogy. However, I'll be happily corrected, if you find any reference :) – vrintle Apr 16 '20 at 17:12