SQL Server – Case-Sensitive search in SQL

If your sql server is installed with the default collation of case-insensitive, you can accomplish this in sql instead.

You can determine if your installation collation is case-insensitive by running this query:


If the result is Collate SQL_Latin1_General_CP1_CI_AS, then it it is case-insensitive.

For example, I may have data stored in column as AbC and ABC. I want only rows where the column = ‘AbC’

You can accomplish this by using the Collate method in sql to tell your installation that you want to override the default property with the case-sensitive property like this (SQL_Latin1_General_CP1_CS_AS):

 FROM dbo.some_table
 WHERE some_value = 'AbC' Collate SQL_Latin1_General_CP1_CS_AS

SQL Server Reporting Services (SSRS) – Alternate row colors with no group, 1 group, or multiple groups

In order to do alternate row coloring for an SSRS report that has grouping, you need an expression for your BackgroundColor property different than the typical:

=iif(rownumber (nothing) mod 2 = 0, "LightGrey", "White")

You can use the following expression now matter how many groupings you have:

=IIF(RunningValue(Fields!somefield.Value,CountDistinct,NOTHING) MOD 2 = 0,"LightGrey", "White")

For multiple grouping, append the fields separated by an &

=IIF(RunningValue(Fields!somefield.Value & Fields!somefield2.Value, CountDistinct, NOTHING) MOD 2 = 0, "White", "LightGrey")

SQL Server – Turning a Comma Separated string into individual rows

Let’s say you have a column in a table that is comma delimited. The value in the column is  ‘APPLE, ORANGE, GRAPE’. You want to break down this string value into individual rows. You may also want to create an auto-incremented IDENTITY column too. Here is one way it can be done.

DECLARE @TempTable TABLE(seq_no int IDENTITY(1,1) PRIMARY KEY, comma_del_string varchar(MAX))
select * from @TempTable

DECLARE @S varchar(max)
DECLARE @Split char(1) = ','

SELECT @S = (select comma_del_string from @TempTable where SEQ_NO = 1)
SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

DECLARE @userData TABLE(seq_no int IDENTITY(1,1) PRIMARY KEY, answer varchar(MAX))
SELECT [Value] = T.c.value('.','varchar(20)') FROM @X.nodes('/root/s') T(c)

select * from @userData

Here is the output: