Today's question comes from Eric:
I was just wondering if there is a way to get an exact comparison of two strings. I am specifically concerned with the case of the strings. Ex. In a database, I have a field called
ad_campaign. Lets say one of the records in the table has a value of
NAC99-etrade. If I select from table where
ad_campaign = "nac99-etrade", I get the record back. But as you notice, the string cases are different. I would normally just do a lower or upper command, but in for this project I have to have it case sensitive. Is there a function or command for this? I have only found one work around and I am not sure about it's stability. It is below:
select * from ad_campaign where unicode(ad_label) = unicode('NAC99-etrade') and ad_label ='NAC99-etrade'
You would think that this would be an easy problem to solve. Unfortunately, case-sensitivity on SQL Server is determined at installation time when you choose the sort order. To do true case-sensitive string comparisons, you'll need to either install a case-sensitive sort order, write a custom comparison routine, or quit caring about case-sensitivity in these strings.
Installing a case-sensitive sort order is um, a bit of work. Check out "Sort order, modifying" topic in books online for a complete list of the hoops you'll need to jump through in order to do this. (Hint, it involves unloading and reloading all of your data by hand.) Also, be prepared for the following side effects:
1) Case-sensitivity is a per-server setting. All databases are affected.
2) All object names in your databases are now case-sensitive. A table named
Foois different from a table named
3) Any other DBA's using the SQL Server are likely to be peeved at you. DBA's aren't exactly known for their consistent capitalization :)
As for the custom compare, you'll need to use
SUBSTRING() to look at each
letter in the string, then compare the
ASCII() value for each letter.
Unless they all match, your strings are different. You can start by
selecting each (case insensitive) match from your main table into a temp
table. Then, use a
WHILE loop for letters 1 to N to keep deleting records
from that temp table where letter n in the table doesn't match letter n in
the comparison string. Any records left after the
WHILE are matching
records. WARNING, this could be slow.
Regarding your work-around - sorry, it doesn't quite work. The
function only returns the integer value of the first letter in the string.
Will work for
NaC99-etrade, etc... as long as
the first letter is capital N, it will match.
A follow-up question... Why does your application care about the case of these strings? That seems a little odd to me.
Here is a suggestion from alert 4Guys reader Mike. This is his suggestion to Eric's problem.
I'm not a SQL Guru so I have to know alot of work-arounds :o).
Here's what I would do:
1. Add a field
ad_campaignto store a unique
2. Create a function to generate the unique searchid
*** the above is a JScript function --- don't know how to write it in a stored procedure ***
3. Each time you update the
ad_label field, you need to update
field (tedious :o))
*** don't forget to initialize searchid for each record to start off
with (real easy)
4. To find what your looking for just perform the search on the unique searchid field: