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%'  --区分大小写

一些常用的SQL语法

--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)