1

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 exists 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?

Roger
  • 4,249
  • 10
  • 42
  • 60
  • Possible duplicate of [How to set Sqlite3 to be case insensitive when string comparing?](http://stackoverflow.com/questions/973541/how-to-set-sqlite3-to-be-case-insensitive-when-string-comparing) – Selvin Mar 14 '17 at 23:33

4 Answers4

3

add collate nocase in your column definition

Selvin
  • 6,598
  • 3
  • 37
  • 43
  • 4
    http://stackoverflow.com/questions/973541/how-to-set-sqlite3-to-be-case-insensitive-when-string-comparing – harmanjd Apr 14 '11 at 14:17
1

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.

Vladimir Ivanov
  • 42,730
  • 18
  • 77
  • 103
0

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

pankajagarwal
  • 13,462
  • 14
  • 54
  • 65
0

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.

harmanjd
  • 1,874
  • 19
  • 21
  • i don't think you should be forced to store data in a specific case just for this purpose. just use a select statement to UPPER each field so they appear to be the same case – james Apr 14 '11 at 14:11
  • @binnyb: but wouldn't that ruin indexing gains? – František Žiačik Apr 14 '11 at 14:16
  • it kind of depends on if you need to index it or not for query performance. I like the 'collate nocase' answer to handle it better. – harmanjd Apr 14 '11 at 14:18