SQL SERVER 筛选时区分大小写的语法

来源：互联网发布：积分数据库设计编辑：程序博客网时间：2024/06/06 01:16

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' columnnameunion allselect 'AAAAA'union allselect '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 aleft 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 aleft 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 aleft 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个字符ABCSELECT LEFT('ABCDEFG',3)--截取字符串RIGHT,截取右边3个字符EFGSELECT RIGHT('ABCDEFG',3)

1 0