When you think ASP, think...
Recent Articles xml
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips
spgif spgif

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
spgif spgif
ASP ASP.NET ASP FAQs Feedback topnav-right
Print this page.
The SQL Guru Answers your Questions...

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 Foo is different from a table named foo.
    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 UNICODE function only returns the integer value of the first letter in the string. So,

select * from ad_campaign 
where unicode(ad_label) = unicode('NAC99-etrade')
      and ad_label ='NAC99-etrade'

Will work for ad_labels of NAC99-etrade, 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.

Good luck!

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 searchid to ad_campaign to store a unique searchid (long integer)

    2. Create a function to generate the unique searchid

    	function getSID(s)
    		var code=0;
    		for (var c=0; c

    *** 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 searchid 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:

    	var sid=getSID("NAC99-etrade");
    	"select * from ad_campaign where searchid = " + sid

Hope this helps, drop me a line and let me know if it worked out!


Read Other SQL Guru Questions

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article