Case Sensitive Query on a Case Insensitive Column in SQL Server

In this example we will use the following table with one case insensitive column:

cs_query_result_contents

When we query data from a case insensitive (CI) table column in SQL Server, we get the result where upper case, lower case and mixed case strings are returned:

SELECT * FROM cs_test
WHERE Text = 'Abc'

ci_query_result

To do a case sensitive (CS) search on a case insensitive table column, use the following query:

SELECT * FROM cs_test
WHERE Text COLLATE SQL_Latin1_General_CP1_CS_AS = 'Abc'

cs_query_result

Column can be made case sensitive when table is designed, using the following query:
CREATE TABLE cs_test (Text nvarchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS)

Querying a column that was designed as case sensitive will be case sensitive:

cs_column_query_result

If the post helped you, please share it:
Pin It

1 comment to Case Sensitive Query on a Case Insensitive Column in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">