Case insensitive search in Microsoft SQL Server

Question

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...

Possible duplicate - stackoverflow.com/questions/1831105/… — vmvadivel
– vmvadivel, Commented Jul 15, 2012 at 4:51

Remus Rusanu · Accepted Answer · 2012-07-14 20:28:16Z

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.

Thanks for you suggestion about not doing UPPER and LOWER comparisons.

devio · Accepted Answer · 2012-07-14 20:37:06Z

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.

Thanks. in finding collations query, it will return nothing. arabic should be replaced with Arabic.

Collectives™ on Stack Overflow

Case insensitive search in Microsoft SQL Server

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related