今天查询sqlite的时候需要不区分大小写，查了下文档，需要使用collate nocase.顺便学习下collate的用法。
sql语句中默认是不区分大小写的，所以语句：
Sql代码
SELECT * FROM RecEngineBizInfo WHERE RecEngineBizName = 'QQ'
和
Sql代码
SELECT * FROM RecEngineBizInfo WHERE RecEngineBizName = 'qq'
查到的结果是一样的。
要查看某个字段是否包含大写字母 A 的脚本为：
Sql代码
select * from RecEngineBizInfo where RecEngineBizName collate Chinese_PRC_CS_AS_WS like '%A%'
要查看某个字段是否包含 大写字母 的脚本为：
Sql代码
SELECT * FROM RecEngineBizInfo rebi
WHERE rebi.RecEngineBizName COLLATE Chinese_PRC_CS_AS
like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'
要查看某个字段是否包含 连续两个大写字母 的脚本为：
Sql代码
SELECT * FROM RecEngineAttributeInfo reai
WHERE reai.ZhiLing COLLATE Chinese_PRC_CS_AS
like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ][ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'
依次类推。
SELECT COUNT(*) FROM dbo.Wd_TagType
SELECT top 500 wt_title,wt_status,wt_statusExt,* FROM dbo.Wd_TagType where wt_title like '%[a-z]%' and Wt_Title like '%[0-9]%' and (len(wt_title)=3 or len(wt_title)=5 or len(wt_title)=7 ) and wt_weight =10 and wt_status =1
SELECT top 5000 wt_id,wt_title,wt_status,wt_statusExt,* FROM dbo.Wd_TagType where (wt_title like '%[A-Z]%' or Wt_Title like '%[0-9]%') and wt_weight <=510 and wt_status =1
SELECT top 5000 wt_id,wt_title,wt_status,wt_statusExt,* FROM dbo.Wd_TagType where ( wt_title COLLATE Chinese_PRC_CS_AI like '%[0-9]%')and wt_status =1
update wd_tagtype set wt_weight= wt_weight+'10000' where wt_id in( 56647,40964,40958,40885,103770,103360,103356,103101,103103,103311,103306,103311,103354,105754,105755,104813,104711,104709,104669,104701,56905,58123,58252,37996,109253,110262,110180,110181,110182,110184,110457,35962,33950,98432,55288,49344,46314,47098,153368,103852)
update wd_tagtype set wt_status=3,Wt_StatusExt='已删除' where wt_title COLLATE Chinese_PRC_CS_AI like '%[0-9]%' and wt_weight <=500 and wt_status =1
update wd_tagtype set wt_status=3,Wt_StatusExt='已删除' where wt_title like '塘厦至广' and wt_weight <=2500 and wt_status =1
最新评论