As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

A basic SQL Question

JasconiusJasconius sword criminalmad onlineRegistered User regular
edited April 2008 in Help / Advice Forum
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.

Jasconius on

Posts

  • Options
    mastmanmastman Registered User regular
    edited April 2008
    where upper(value) = upper(value)

    mastman on
    ByalIX8.png
    B.net: Kusanku
  • Options
    VThornheartVThornheart Registered User regular
    edited April 2008
    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. ;)

    VThornheart on
    3DS Friend Code: 1950-8938-9095
  • Options
    JasconiusJasconius sword criminal mad onlineRegistered User regular
    edited April 2008
    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?

    Jasconius on
  • Options
    VThornheartVThornheart Registered User regular
    edited April 2008
    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?

    VThornheart on
    3DS Friend Code: 1950-8938-9095
  • Options
    buzzard0627buzzard0627 Registered User regular
    edited April 2008
    Typically MS SQL is case insensitive, however, it depends on your collation.

    buzzard0627 on
Sign In or Register to comment.