//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
sqlserver2000.databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Sql Server 2000 :: How can I make my SQL queries case sensitive?


How can I make my SQL queries case sensitive?

If you installed SQL Server with the default collation options, you might find that the following queries return the same results: 
 
CREATE TABLE mytable 

    mycolumn VARCHAR(10) 

GO 
 
SET NOCOUNT ON 
 
INSERT mytable VALUES('Case') 
GO 
 
SELECT mycolumn FROM mytable WHERE mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE mycolumn='caSE' 
SELECT mycolumn FROM mytable WHERE mycolumn='case'
 
You can alter your query by forcing collation at the column level: 
 
SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'caSE' 
 
SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'case' 
 
SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'Case' 
 
-- if myColumn has an index, you will likely benefit by adding 
-- AND myColumn = 'case'
 
If you want to do this in a more global way, instead of modifying each individual query, you can force the collation at the database level, or at the column level, using the ALTER DATABASE and ALTER TABLE commands, respectively. You can see the current collation level on the properties tab of the database server, through Enterprise Manager (if you're going to change this setting, MAKE NOTE OF THIS VALUE): 
 
 
And you can see the description from running the following query: 
 
SELECT DATABASEPROPERTYEX('<database name>', 'Collation')
 
As changing this setting can impact applications and SQL queries, I would isolate this test first. In SQL Server 2000, you can easily run an ALTER TABLE statement to change the sort order of a specific column, forcing it to be case sensitive. First, execute the following query to determine what you need to change it back to: 
 
EXEC sp_help 'mytable'
 
The second recordset should contain the following information, in a default scenario: 
 
Column_Name Collation 
----------- ---------------------------------------------- 
mycolumn    SQL_Latin1_General_CP1_CI_AS
 
Whatever the 'Collation' column returns, you now know what you need to change it back to after you make the following change, which will force case sensitivity: 
 
ALTER TABLE mytable 
    ALTER COLUMN mycolumn VARCHAR(10) 
    COLLATE Latin1_General_CS_AS 
GO 
 
SELECT mycolumn FROM mytable WHERE mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE mycolumn='caSE' 
SELECT mycolumn FROM mytable WHERE mycolumn='case'
 
If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously): 
 
ALTER TABLE mytable 
    ALTER COLUMN mycolumn VARCHAR(10) 
    COLLATE SQL_Latin1_General_CP1_CI_AS
 
If you are stuck with SQL Server 7.0, you can try this workaround, which might be a little more of a performance hit (you should only get a result for the FIRST match): 
 
SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10)) 
 
SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10)) 
 
SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10)) 
 
-- if myColumn has an index, you will likely benefit by adding 
-- AND myColumn = 'case'

Related Articles

Are there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?

 

 


Created: 6/4/2001 | Last Updated: 8/27/2004 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (76)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...