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

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。