3

In my database which is utf8_general_ci, 99.99% of searches should be done case insensitive. Now there's a specific situation where I need to find some data in a case sensitive manner. The field is a varchar field, which I usually search case insensitive.

My question is: Can I perform a case sensitive search on a field that is usually case insensitive?

Moak
  • 12,596
  • 27
  • 111
  • 166

4 Answers4

6

select * from page where convert(pageTitle using latin1) collate utf8_ = 'Something'

i found this answer descriptive enough :)

Community
  • 1
  • 1
ankur.singh
  • 658
  • 5
  • 11
  • 2
    OK however (Unknown collation: 'utf8_') didnt work so I changed it to `FROM \`table\` WHERE convert(\`collumn\` using latin1) COLLATE latin1_general_cs LIKE '%wOrd'` – Moak Apr 22 '11 at 08:08
  • Actually i just copied it from that answer... was for reference only – ankur.singh Apr 22 '11 at 08:12
  • It will cause performance issues but as you are using it in 0.01% cases only :) i dont think it will matter much to you ... between update us in case you find any better solution. – ankur.singh Apr 22 '11 at 08:45
  • An example with utf8 could be "SELECT * FROM `my_table` WHERE CONVERT(`my_value` USING utf8) COLLATE utf8_general_ci LIKE '%Something%'" – scott Jul 18 '12 at 14:54
4

You have to change the collation of operands which are used in the search query using COLLATE operator

OR

If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation.

See here http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

Shakti Singh
  • 84,385
  • 21
  • 134
  • 153
  • This looks right, however the mysql manual is not very clear where to apply this `COLLATE` to a `SELECT` statement from a Table – Moak Apr 22 '11 at 07:59
  • SELECT name FROM table WHERE CONVERT(name USING utf8) COLLATE utf8_general_cs LIKE "%anne%"; Note the _cs suffix. – Will Martin Apr 22 '11 at 08:07
  • `#1273 - Unknown collation: 'utf8_general_bin'` thanks though, I used latin1 then it worked – Moak Apr 22 '11 at 08:11
3

I would suggest changing the collation type so that it becomes case sensitive and then changing your code in the 99.99% of other cases. For information on changing the case of the field check: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

You can use the LOWER() and UPPER() functions in SQL to standardise the case for insensitive searches once the field type is set to be case sensitive.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Mark Aroni
  • 605
  • 1
  • 10
  • 21
  • 1
    Be aware that LOWER/UPPER will render an index unusable on the column, if one exists. – OMG Ponies Apr 22 '11 at 07:41
  • This does not really answer my question. I've edited my question for clarity. I'm afraid it's not an option at this stage to go back and change all the code, and my column is indexed and needs to be insensitive, so OMG Ponies comment is a real risk for me. – Moak Apr 22 '11 at 07:50
  • I see, in that case I would go with the answer from @ankur.singh – Mark Aroni Apr 22 '11 at 07:59
1

You can make a query case sensitive, for example if you want to check for userid and password:

Select * from TableName Where WHERE LoginId=@userid COLLATE SQL_Latin1_General_CP1_CS_AS AND Password=@password COLLATE SQL_Latin1_General_CP1_CS_AS

it checks for userid and password and returns the values if they are true (case sensitive).

Flexo
  • 87,323
  • 22
  • 191
  • 272
Krishna Thota
  • 6,646
  • 14
  • 54
  • 79