A basic SQL Question

Jasconius

Is there a way to do case insensitive comparison in SQL?

Example, I am storing user names that can have capitalization and I want to do checks to see if names exist, but I don't want someone to be able to have the usernames

jasconius

and

Jasconius or jaSCONius

So when I check my database to see if the name exists I can only pass it a single parameter, so how to I match this parameter (presumably in all lowercase) against existing usernames that potentially have capitalization..

This is using MSSQL Server 2005 if it matters.

*edit*

It seems to me that it might be doing that by default, but I would just like to have some confirmation.

mastman

where upper(value) = upper(value)

VThornheart

Aye, it does it by default in MSSQL 2005. Be careful, because this can lead to very subtle bugs. I learned that the *hard* way a year or so ago.

Jasconius

VThornheart wrote: »

Aye, it does it by default in MSSQL 2005. Be careful, because this can lead to very subtle bugs. I learned that the *hard* way a year or so ago.

Well i am finding that when you execute the query directly in SQL Management Studio it appears to work by default, but for some reason when I am hitting it from .NET it is definitely NOT working right now. Even with the upper() clauses.

So.

I'm going to have to step through some stuff to see what the deal is.

Do the SQL side upper/lower clauses have any special treatment of numeric characters that I should be aware of?

VThornheart

Hmm... if it's not a security risk to do so, could you post the query as you do it in the Management Studio as well as the relevant lines when you do it in code?

buzzard0627

Typically MS SQL is case insensitive, however, it depends on your collation.