SQL SERVER 篩選時區分大小寫的文法

Demo：

--SELECT * FROM 表名 WHERE 列名 COLLATE Chinese_PRC_CS_AS ='篩選字元'  
--SELECT * FROM 表名 WHERE 列名 COLLATE Chinese_PRC_CS_AS like '%篩選字元%'  
--SELECT * FROM 表名 WHERE 列名 COLLATE Chinese_PRC_CS_AS in('篩選字元1','篩選字元2')


--執行以下代碼檢視效果
;with dt as(
select 'aaaaa' columnname
union all
select 'AAAAA'
union all
select 'AAaaa'
)
--select * from dt where columnname like '%aaaaa%'--不區分大小寫
select * from dt where columnname COLLATE Chinese_PRC_CS_AS like '%aaaaa%'  --區分大小寫      

--left join 一對多查詢
select a.ProductID,a.ProductName,a.Spec,b.ProductID,b.ProductName,b.Spec from vw_Stock_CompanyProduct a
left join vw_Stock_CompanyProduct b on a.CompanyID=b.CompanyID and a.Spec COLLATE Chinese_PRC_CS_AS=b.Spec and b.sc_type=2 and b.IsEnable=1 and ((a.ProductName+'-冷镦')=b.ProductName OR (a.ProductName+'-熱處理')=b.ProductName)
where a.CompanyID=344 and a.CPID=125818


--子產品臨時查詢
;with dt as(
select a.ProductID,a.ProductName,a.Spec,b.ProductID as ProductID_01,b.ProductName as ProductName_01,b.Spec as Spec_01 from vw_Stock_CompanyProduct a
left join vw_Stock_CompanyProduct b on a.CompanyID=b.CompanyID and a.Spec COLLATE Chinese_PRC_CS_AS=b.Spec and b.sc_type=2 and b.IsEnable=1 and ((a.ProductName+'-冷镦')=b.ProductName OR (a.ProductName+'-熱處理')=b.ProductName)
where a.CompanyID=344 and a.CPID=125818
)
select * from dt


--CASE WHEN 判斷語句子產品
;with dt01 as(
select a.ProductID,a.ProductName,a.Spec,b.ProductID as ProductID_01,b.ProductName as ProductName_01,b.Spec as Spec_01 from vw_Stock_CompanyProduct a
left join vw_Stock_CompanyProduct b on a.CompanyID=b.CompanyID and a.Spec COLLATE Chinese_PRC_CS_AS=b.Spec and b.sc_type=2 and b.IsEnable=1 and ((a.ProductName+'-冷镦')=b.ProductName OR (a.ProductName+'-熱處理')=b.ProductName)
where a.CompanyID=344 and a.CPID=125818
)
select *,(CASE WHEN RIGHT(ProductName_01,3)='-冷镦' THEN 1401 WHEN RIGHT(ProductName_01,4)='-熱處理' THEN 1402 END) as Step_ID from dt01



--截取字元串LEFT,截取左邊3個字元ABC
SELECT LEFT('ABCDEFG',3)


--截取字元串RIGHT,截取右邊3個字元EFG
SELECT RIGHT('ABCDEFG',3)