A co-worker of mine sent me a bit of code where he had the need to do case sensitive comparisons of a column during a join. What he did to solve this was to set the collation of the columns to case sensitive, run the join, and then re-set them to the same collation.
I think this is a pretty bad idea, considering that if there was an index on the columns it might do something bad to the index, and it is changing the collation on a table in a system. The proc was also not being run from inside of the transaction, so if the code in between the collation changes fails, then the table is in the incorrect collation. There are plenty of things wrong with this, but those are a few.
What I did was create a small example of how collation works when doing comparisons in the case of case sensitivity. Below is a quick example records that match with case sensitivity and some that do not match with case sensitivity, and then give an example of comparisons done using strict case sensitivity, and what it looks like if you cast a record to varbinary, and then match it.
I find that casting a record to varbinary is usually the best method of doing a case sensitive comparison. Yes, it causes a table scan, but I think if an index is required for the case sensitive row, then perhaps the column itself should be case sensitive.
Here is some useful code to show case sensitive matching. Depending on the default collation of your own database, the column matchcurrcolation can return different results.
IF object_id('tempdb..#truthtest') IS NOT NULL
I think this is a pretty bad idea, considering that if there was an index on the columns it might do something bad to the index, and it is changing the collation on a table in a system. The proc was also not being run from inside of the transaction, so if the code in between the collation changes fails, then the table is in the incorrect collation. There are plenty of things wrong with this, but those are a few.
What I did was create a small example of how collation works when doing comparisons in the case of case sensitivity. Below is a quick example records that match with case sensitivity and some that do not match with case sensitivity, and then give an example of comparisons done using strict case sensitivity, and what it looks like if you cast a record to varbinary, and then match it.
I find that casting a record to varbinary is usually the best method of doing a case sensitive comparison. Yes, it causes a table scan, but I think if an index is required for the case sensitive row, then perhaps the column itself should be case sensitive.
Here is some useful code to show case sensitive matching. Depending on the default collation of your own database, the column matchcurrcolation can return different results.
IF object_id('tempdb..#truthtest') IS NOT NULL
BEGIN
DROP TABLE #truthtest
END
create table #truthtest
(
id int identity(1,1)
,character1 varchar(10)
, character2 varchar(10)
)
insert into #truthtest
(
character1
,character2
)
SELECT
'A'
,'a'
UNION ALL
SELECT 'a','a'
UNION ALL
SELECT 'a','A'
UNION ALL
select 'A','a'
UNION ALL
select 'aBcDe','AbCdE'
UNION ALL
select 'abcde','ABCDE'
UNION ALL
select 'aBcDe','aBcDe'
SELECT
*
, case when character1 = character2 then 1 else 0 end as matchcurrcolation
, case when CAST(character1 as varbinary) = CAST(character2 as varbinary) then 1 else 0 end as matchCScolation
, case when CAST(UPPER(character1) as varbinary) = CAST(UPPER(character2) as varbinary) then 1 else 0 end as matchCIcolation
FROM #truthtest
