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
  • 14,873
  • 19
  • 71
  • 119
  • 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
  • 288,378
  • 40
  • 442
  • 569
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,858
  • 7
  • 80
  • 143