1

I have a database with Arabic_BIN collation on Microsoft SQL Server 2005.

If I run this query:

SELECT ID FROM maj_Users WHERE UserName = 'mahdi'

I'll get nothing, but if I run this query:

SELECT ID FROM maj_Users WHERE UserName = 'Mahdi'

I'll get a cell... Seems that SQL Server is searching database case-sensitively, but I want to search database case-insensitively.

Is there any way?

EDIT: Also I have tried SELECT ID FROM maj_Users WHERE UserName LIKE 'mahdi' but it didn't work...

Mahdi Ghiasi
  • 13,903
  • 17
  • 65
  • 118
  • Possible duplicate - http://stackoverflow.com/questions/1831105/how-to-do-a-case-sensitive-search-in-where-clause-im-using-sql-server – vmvadivel Jul 15 '12 at 04:51

2 Answers2

8

SQL Server is not searching the database in a case sensitive manner, nor it does it on a case insensitive one. It does it according to the collation of the column involved. There are case sensitive and case insensitive collation. I recommend you read Collation and Unicode Support. Do not start doing UPPER or LOWER comparisons, that is not the right approach and it has serious performance implications due to sarg-ability.

Remus Rusanu
  • 281,117
  • 39
  • 423
  • 553
6

You can override a COLLATION defined on database or column level in a comparison using the COLLATE keyword:

SELECT ID FROM maj_Users WHERE UserName = 'Mahdi' COLLATE [collation name]

To find collations for case-insensitive Arabic, use the query

select * from fn_helpcollations()
where name like 'Arabic%' and name like '%CI%'

Arabic_CI_AI or Arabic_100_CI_AI seems to be the right choice for you.

devio
  • 36,179
  • 7
  • 78
  • 140