lowercase Matching in Ms sql

U

usjTraffic

Guppy
Hi,

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.:)
 
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
where field [username] is all lower case.
 
4u2ges said:
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
where field [username] is all lower case.
Click to expand...


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:
WHERE (CONVERT(BINARY, account) = CONVERT(BINARY, LOWER('material')))
Click to expand...
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:
WHERE (CONVERT(BINARY, account) = CONVERT(BINARY, 'material'))
Click to expand...
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!
..
 
You must log in or register to reply here.
Back
Top