sqlserver常用函数

最新推荐文章于 2023-07-24 09:47:11 发布

andylaudotnet

最新推荐文章于 2023-07-24 09:47:11 发布

阅读量591

点赞数

分类专栏： Sqlserver内容文章标签： sqlserver table delete insert integer null

Sqlserver内容专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1:replace 函数
第一个参数你的字符串，第二个参数你想替换的部分，第三个参数你要替换成什么
select replace('lihan','a','b')

-----------------------------
lihbn

（所影响的行数为 1 行）
=========================================================
2:substring函数
第一个参数你的字符串，第二个是开始替换位置，第三个结束替换位置
select substring('lihan',0,3);
-----
li

（所影响的行数为 1 行）
=========================================================
3:charindex函数
第一个参数你要查找的char，第二个参数你被查找的字符串返回参数一在参数二的位置
select charindex('a','lihan')
-----------
4

（所影响的行数为 1 行）

===========================================================
4：ASCII函数
返回字符表达式中最左侧的字符的 ASCII 代码值。
select ASCII('lihan')
-----------
108

（所影响的行数为 1 行）

================================================================
5：nchar函数
根据 Unicode 标准的定义，返回具有指定的整数代码的 Unicode 字符。
参数是介于 0 与 65535 之间的正整数。如果指定了超出此范围的值，将返回 NULL。
select nchar(3213)
----
unicode字符

（所影响的行数为 1 行）

=========================================================
6：soundex
返回一个由四个字符组成的代码 (SOUNDEX)，用于评估两个字符串的相似性。
SELECT SOUNDEX ('lihan'), SOUNDEX ('lihon');
----- -----
L546 L542

（所影响的行数为 1 行）
=========================================================
7：char
参数为介于 0 和 255 之间的整数。如果该整数表达式不在此范围内，将返回 NULL 值。
SELECT char(125)
----
}

（所影响的行数为 1 行）

==========================================================
8：str函数
第一个参数必须为数字，第二个参数表示转化成char型占的位置，小于参数一位置返回*，大于右对齐
SELECT str(12345,3)
----
***

（所影响的行数为 1 行）

SELECT str(12345,12)
------------
       12345

（所影响的行数为 1 行）
===========================================================
9：difference函数
返回一个整数值，指示两个字符表达式的 SOUNDEX 值之间的差异。
返回的整数是 SOUNDEX 值中相同字符的个数。返回的值从 0 到 4 不等：0 表示几乎不同或完全不同，4 表示几乎相同或完全相同。
SELECT difference('lihan','liha')
-----------
3

（所影响的行数为 1 行）

==================================================================
10：stuff函数（四个参数）
函数将字符串插入另一字符串。它在第一个字符串中从开始位置删除指定长度的字符；然后将第二个字符串插入第一个字符串的开始位置。
SELECT stuff('lihan',2,3,'lihan')
--------
llihann

（所影响的行数为 1 行）
===============================================================

11：left函数
返回最左边N个字符，由参数决定
select left('lihan',4)
-----
liha

（所影响的行数为 1 行）
================================================================

12 right函数
返回最右边N个字符，由参数决定
select right('lihan',4)
-----
ihan

（所影响的行数为 1 行）
================================================================

13：replicate函数
我的认为是把参数一复制参数二次
select replicate('lihan',4)
--------------------
lihanlihanlihanlihan

（所影响的行数为 1 行）

================================================================

14：len函数
返回参数长度
select len('lihan')
-----------
5

（所影响的行数为 1 行）

================================================================
15：reverse函数
反转字符串
select reverse('lihan')
-----
nahil

（所影响的行数为 1 行）

=================================================================

16：lower和upper函数
参数大小写转化
select lower(upper('lihan'))
--------------------
lihan

（所影响的行数为 1 行）

====================================================================

17：ltrim和rtrim函数
删除左边空格和右面空格
select ltrim('    lihan                 ')
--------------------------
lihan

（所影响的行数为 1 行）
select rtrim('    lihan')
---------
    lihan

（所影响的行数为 1 行）

18. PATINDEX函数
返回字符或者字符串在另一个字符串或者表达式中的起始位置，PATINDEX函数支持搜索字符串中使用通配符，这使PATINDEX函数对于变化的搜索字符串很有价值。第一参数之前和之后必须有 % 字符（搜索第一个或最后一个字符时除外）。

select patindex('%ha%','lihan')

-----------
3

（所影响的行数为 1 行）

create table tb(id int,name varchar(4))
insert tb select 1,'aa'
union all select 1,'aa'
union all select 2,'bb'
union all select 3,'bb'
union all select 4,'cc'
union all select 1,'aa'
union all select 4,'cc'

select * from tb

select distinct * into # from tb
drop table tb
select * into tb from #

delete from tb
where id in
    (
    select id from tb b
    where exists(select * from tb where b.name=name and b.id>id)
    )

select * from tb

drop table #
drop table tb

/*

（所影响的行数为 7 行）

id          name
----------- ----
1           aa
1           aa
2           bb
3           bb
4           cc
1           aa
4           cc

（所影响的行数为 7 行）

（所影响的行数为 4 行）

（所影响的行数为 4 行）

（所影响的行数为 1 行）

id          name
----------- ----
1           aa
2           bb
4           cc

（所影响的行数为 3 行）
*/

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

比方说在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
Select Name,Count(*) From A Group By Name Having Count(*) > 1

如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1
------------------------------------------------------------------------------------------------
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

方法二
　　有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
　　1、对于第一种重复，比较容易解决，使用
select distinct * from tableName
　　就可以得到无重复记录的结果集。
　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
　　发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。

　　2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下
　　假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
　　最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）
select * from tablename where id in (
select id from tablename
group by id
having count(id) > 1)

declare @table table (id int,name nvarchar(10))
insert into @table select 1,'aa'union all select 1,'aa'
        union all select 2,'bb'
        union all select 3,'bb'
        union all select 4,'cc'
        union all select 1,'aa'
        union all select 4,'cc'
select a.id,a.name from
(select ROW_NUMBER()over (order by id) 序号, * from @table a) a
   where not exists (select 1 from (select ROW_NUMBER()over (order by id) 序号, * from @table a) b
   where a.id=b.id and a.name=b.name and a.序号<b.序号 )
id          name
----------- ----------
1           aa
2           bb
3           bb
4           cc

(4 行受影响)

经典尝试

declare @table table (id int,name nvarchar(10))
insert into @table select 1,'aa'union all select 1,'aa'
        union all select 2,'bb'
        union all select 3,'bb'
        union all select 4,'cc'
        union all select 1,'aa'
        union all select 4,'cc'

delete a
from (
    select id,name,rn = row_number() over (partition by id,name order by id) from @table
) a where rn > 1

select * from @table

id          name
----------- ----------
1           aa
2           bb
3           bb
4           cc

(4 row(s) affected)

delete aaa
where id, name in (
select id, name from (
    select count(1), id, name
    from aaa
    group by id, name
    having count(1) > 1
)
)

只是速度会比较慢。只要不是量大的日子就不怕。

Delete ChongFu Where ID not in
(Select max(ID) From ChongFU Group By UName)

DELETE FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2)

这是一句话，相信你能看懂！

DELETE tb a WHERE ROWID NOT IN (SELECT MIN(ROWID)
                       FROM tb b
                      WHERE b.id = a.id
                        AND b.name = a.name
                      GROUP BY b.id, b.name
                     )
;

delete FROM tb E
　　WHERE E.ROWID > (SELECT MIN(X.ROWID)
　　FROM tb X
　　WHERE X.name = E.name and X.id = E.id);

andylaudotnet

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
sqlserver常用函数

1:replace 函数第一个参数你的字符串，第二个参数你想替换的部分，第三个参数你要替换成什么select replace(lihan,a,b) -----------------------------lihbn（所影响的行数为 1 行）=======
复制链接

扫一扫

专栏目录