CODEWITHSUNDEEP
mysqlsqloperatorsequals
Oliver Spryn
Oliver Spryn

Reputation: 17368

MySQL Is Strictly Equal to Operator?

In many programming languages there is the basic equals operator which will see if the text of two strings are equal:

if ("Hi" == "hi") {
  //this code runs
}

But there is also the strict equal to operator:

if ("Hi" === "hi") {
  //this code will never run
}

Is there an equivalent operator for the above code snippet in MySQL? The = operator just doesn't cut it. If I run:

SELECT * FROM users WHERE name = 'john';

MySQL will return rows with a John, with a capital "J".

Thank you for your time.

Upvotes: 7

Views: 8847

Answers (2)

zessx
zessx

Reputation: 68810

You can use STRCMP :

SELECT * FROM users WHERE STRCMP(name, 'john') = 0;

Note :

  • 0 = same
  • -1 = first element smaller
  • 1 = first element bigger

You must use COLLATES if you're using MySQL 4.0+ (STRCMP is no longer case sensitive) :

  • COLLATE utf8_general_ci = case insensitive (ci)
  • COLLATE utf8_general_cs = case sensitive (cs)

Upvotes: 3

Zane Bien
Zane Bien

Reputation: 23125

You can use the COLLATE operator to convert the column to a case-sensitive collation:

SELECT * FROM users WHERE name LIKE 'john' COLLATE utf8_bin

MySQL documentation on case sensitivity.

Upvotes: 12

Related Questions