mssql整理

最新推荐文章于 2024-08-27 14:28:37 发布

weixin_34179968

最新推荐文章于 2024-08-27 14:28:37 发布

阅读量62

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/microtiger/p/6208088.html

本文介绍了一系列SQL查询和数据处理技巧，包括如何筛选包含特定字符的数据记录、按长度删除记录、提取特定姓氏的用户等。此外，还提供了一些实用的例子来帮助理解如何使用SQL进行高效的数据管理和筛选。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select charindex( 'a ', 'bcad ')

1 删除女性数据
2.
SELECT * FROM Group2 where PATINDEX('%[吖-做]%',[Nick])=0 找出不包含中文汉字的记录

SELECT * FROM Group2 where not PATINDEX('%[吖-做]%',[Nick])=0 找出有包含中文汉字的记录
2 删除长度小于4 或者大于6的
delete from Group2 where datalength(Nick)<4 delete from Group2 where datalength(Nick)=5 delete from Group2 where datalength(Nick)>6
3 删除含有26个字母和10个数字的
delete from tbl where charindex('a',ziduan)>0
4 写出含有百家姓的
select top 1 substring(Nick,0,2) as N from Group11 where id=20

insert into qq_number(QQNum,Nick,Gender,Age) select QQNum,Nick,Gender,Age from Group2 where substring(Nick,0,2)='王'

一：查询字段首位是否为汉字？
SELECT * FROM 表名WHERE ascii(字段)>127
二：查询字段是否包含汉字（任意位）？
select * from 表名 where 字段 like '%[吖-座]%'
这个可以判断是否包含汉字而不是其它双字节字符
[吖-座]是中文字符集第一个到最后一个的范围，虽然匹配准确，但是用Like的效率很低

1.删除不包含中文的
delete from Group2 where PATINDEX('%[吖-做]%',[Nick])=0
2 删除长度小于4 或者大于6的
delete from Group2 where datalength(Nick)<4; delete from Group2 where datalength(Nick)=5; delete from Group2 where datalength(Nick)>6;
3 删除含有26个字母和10个数字的
delete from Group2 where charindex('a',Nick)>0;
delete from Group2 where charindex('b',Nick)>0;
delete from Group2 where charindex('c',Nick)>0;
delete from Group2 where charindex('d',Nick)>0;
delete from Group2 where charindex('e',Nick)>0;
delete from Group2 where charindex('f',Nick)>0;
delete from Group2 where charindex('g',Nick)>0;
delete from Group2 where charindex('h',Nick)>0;
delete from Group2 where charindex('i',Nick)>0;
delete from Group2 where charindex('j',Nick)>0;
delete from Group2 where charindex('k',Nick)>0;
delete from Group2 where charindex('l',Nick)>0;
delete from Group2 where charindex('m',Nick)>0;
delete from Group2 where charindex('n',Nick)>0;
delete from Group2 where charindex('o',Nick)>0;
delete from Group2 where charindex('p',Nick)>0;
delete from Group2 where charindex('q',Nick)>0;
delete from Group2 where charindex('r',Nick)>0;
delete from Group2 where charindex('s',Nick)>0;
delete from Group2 where charindex('t',Nick)>0;
delete from Group2 where charindex('u',Nick)>0;
delete from Group2 where charindex('v',Nick)>0;
delete from Group2 where charindex('w',Nick)>0;
delete from Group2 where charindex('x',Nick)>0;
delete from Group2 where charindex('y',Nick)>0;
delete from Group2 where charindex('z',Nick)>0;
delete from Group2 where charindex('0',Nick)>0;
delete from Group2 where charindex('1',Nick)>0;
delete from Group2 where charindex('2',Nick)>0;
delete from Group2 where charindex('3',Nick)>0;
delete from Group2 where charindex('4',Nick)>0;
delete from Group2 where charindex('5',Nick)>0;
delete from Group2 where charindex('6',Nick)>0;
delete from Group2 where charindex('7',Nick)>0;
delete from Group2 where charindex('8',Nick)>0;
delete from Group2 where charindex('9',Nick)>0;
4
insert into qq_num(QQNum,Nick,Gender) select QQNum,Nick,Gender from Group2 where substring(Nick,0,2)='王'