Avoid duplicate entries without being case-sensitive?

How to avoid duplicate entries without being case-sensitive?

If somebody inputs "Blue" and "Blue" already exists in the database, I don't insert the new input to avoid duplicates.

However, if somebody inputs "BlUe", my query to check if the input already exis开发者_C百科ts returns false and it is inserted.

Without apply strict formatting, ie: capitalizing the first letter of each word and lowercasing the rest, is there a better way to do this?

---

add collate nocase in your column definition

---

Usually this work is done from both sides: you make upper case before pass the value to the database and in sql also use TO_UPPERCASE.

---

try the where clause as something like this. where UPPER('BLUe') = UPPER(colValue)

---

In this case, I would store values in the database in a single case (probably lower) and do you comparison case-insensitive. For display you can just save the display value as the user entered it (keeping mixed case), or you can do formatting for the case you want.