lowercase Matching in Ms sql

Discussion in 'Microsoft Development' started by usjTraffic, Jul 20, 2006.

  1. usjTraffic

    usjTraffic Guppy


    I have been trying to find a function in Ms SQL 2000 to pick up records that match exactly to a string in lowercase.

    So far, I found that Query Analyser that works closely with the MS SQL Server2000 converts any lowercase values and displays it in uppercase thereby not producing the match required.

    When I made a query via the "Query" method (using Enterprise Manager) to display a table, it displays the values in both uppercase and lowercase, meaning the actual content was either stored in upper or lower case as the case may be, and not forced to be stored in any particular case, hence displayed as they were stored.

    Because of that, I just want to display only the records that were stored in lowercase but not those records stored in uppercase. Sounds simple isn't it?

    There must be a way, I have not found out yet. Anyone have cracked this before? I will appreciate if you can share how you do it.

    Thank you in advance.:)
  2. 4u2ges

    4u2ges Perch

    Rather simple. To match the case you should convert value to binary.

    SELECT username FROM table WHERE CONVERT(BINARY, username) = CONVERT(BINARY, LOWER(username))

    This would only return records from [table] where field [username] is all lower case.
  3. antic

    antic Perch

    4u, thanks, great stuff. I'll be keeping that one in mind.
  4. usjTraffic

    usjTraffic Guppy

    Thank you 4u2ges :),
    I tried it and it works. I modified it slightly to suit my scenario as the field contained alpha as well as numeric. Because of that, if displayed numeric as well.
    In my case, the query looked like this:
    Fortunately I only wanted a specific match, so I didn't have to crack on a more generic case which eliminates numeric values. I'm sure a slightly modified "Where" clause could solve this.:D Went further to drop 'LOWER('material')' keeping only the literal still worked fine, like so:
    Your solution was an excellent example of a 'not-so-obvious' use of the a function. I like that solution.:) Will keep this in mind... very useful. :p Saved me a lot of searching and cracking...:p Thanks!
  5. 4u2ges

    4u2ges Perch

    sure thing guys :)

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services