Case Sensitive

Transact-SQL
15

And then there is a third case ... applying collation in BOTH places.

If you ONLY have collation on the result of the CASE then the case will have already changed anything begining with "PCSDT" (i.e. case Insensitive) to "PCSDT" (Upper Case), so the distinct will only list the upper case variant.

If you have COLLATE in both places then you see all case variants

SELECT	[T_ID] = IDENTITY(int, 1, 1),
	*
INTO #TEMP
FROM
(
	SELECT [Readcode] = 'PCSDT'
	UNION ALL SELECT 'pCSDT'
	UNION ALL SELECT 'pcsdt'
	UNION ALL SELECT 'Xcsdt'
	UNION ALL SELECT 'PCSDTabcd'
	UNION ALL SELECT 'pCSDTabcd'
) AS X


-- Show the sample data:
SELECT	*
FROM	#TEMP

-- Test 1
SELECT	T_ID,
	Readcode,
	CASE WHEN left(Readcode,5)= 'PCSDT' 
		THEN 'PCSDT'
		ELSE Readcode 
	END COLLATE Latin1_General_BIN2 AS Readcode1,
	CASE WHEN left(Readcode,5)= 'PCSDT'  COLLATE Latin1_General_BIN2
		THEN 'PCSDT'
		ELSE Readcode 
	END AS Readcode2
FROM	#TEMP

-- Test 2
SELECT DISTINCT
-- SELECT T_ID,
	CASE WHEN left(Readcode,5)= 'PCSDT' 
		THEN 'PCSDT'
		ELSE Readcode 
	END COLLATE Latin1_General_BIN2 AS Readcode1
FROM	#TEMP
--
SELECT DISTINCT
-- SELECT T_ID,
	CASE WHEN left(Readcode,5)= 'PCSDT'  COLLATE Latin1_General_BIN2
		THEN 'PCSDT'
		ELSE Readcode 
	END AS Readcode2
FROM	#TEMP
--
SELECT DISTINCT
-- SELECT T_ID,
	CASE WHEN left(Readcode,5)= 'PCSDT'  COLLATE Latin1_General_BIN2
		THEN 'PCSDT'
		ELSE Readcode 
	END COLLATE Latin1_General_BIN2 AS Readcode3
FROM	#TEMP

gives

T_ID        Readcode  
----------- --------- 
1           PCSDT
2           pCSDT
3           pcsdt
4           Xcsdt
5           PCSDTabcd
6           pCSDTabcd

T_ID        Readcode  Readcode1 Readcode2 
----------- --------- --------- --------- 
1           PCSDT     PCSDT     PCSDT
2           pCSDT     PCSDT     pCSDT
3           pcsdt     PCSDT     pcsdt
4           Xcsdt     Xcsdt     Xcsdt
5           PCSDTabcd PCSDT     PCSDT
6           pCSDTabcd PCSDT     pCSDTabcd

Readcode1 
--------- 
PCSDT
Xcsdt

Readcode2 
--------- 
PCSDT
pCSDTabcd
Xcsdt

Readcode3 
--------- 
PCSDT
Xcsdt
pCSDT
pCSDTabcd
pcsdt