27,579
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[BMDH]
if object_id('[BMDH]') is not null drop table [BMDH]
create table [BMDH]([姓名] varchar(100),[电话] varchar(14))
insert [BMDH]
select '张三,赵一脚D4.鬼人,张三','5566742' union all
select '小九C3.龙,李四','13088889999' union all
select '红河道.换采,风风.EE,我是风儿.狼月,赵五','13877776666' union all
select '小九C3.龙,ZZZZ.天,鬼子六','3322152' union all
select '风风.EE,我是风儿.狼月,赵五','15987489999'
--> 测试数据:[QQMA]
if object_id('[QQMA]') is not null drop table [QQMA]
create table [QQMA]([姓名] varchar(6),[电话] varchar(14))
insert [QQMA]
select '张三','5566742' union all
select '李四','13088889999' union all
select '赵五','13877776666' union all
select '鬼子六','3322152'
update [BMDH]
set [姓名]=case when RIGHT([BMDH].[姓名],charindex(',',REVERSE([BMDH].[姓名]))-1)=a.[姓名]
then LEFT([BMDH].[姓名],LEN([BMDH].[姓名])
-charindex(',',REVERSE([BMDH].[姓名]))) else [BMDH].[姓名] end
from [QQMA] a where [BMDH].电话=a.电话
select * from [BMDH]
/*
姓名 电话
张三,赵一脚D4.鬼人 5566742
小九C3.龙 13088889999
红河道.换采,风风.EE,我是风儿.狼月 13877776666
小九C3.龙,ZZZZ.天 3322152
风风.EE,我是风儿.狼月,赵五 15987489999
*/
create table BMDH (name nvarchar(1200),phone varchar(120))
go
create table QQMA (name nvarchar(1200),phone varchar(120))
go
insert BMDH
select '张三,赵一脚D4.鬼人,张三','5566742' union
select '小九C3.龙,李四','13088889999'union
select '红河道.换采,风风.EE,我是风儿.狼月,赵五','13877776666'union
select '小九C3.龙, ZZZZ.天,鬼子六','3322152' union
select '风风.EE,我是风儿.狼月,赵五','15987489999'
insert QQMA
select '张三','5566742'union
select '李四','13088889999'union
select '赵五','13877776666'union
select '鬼子六','3322152'
go
select * from BMDH order by name desc
/*
name phone
张三,赵一脚D4.鬼人,张三 5566742
小九C3.龙,李四 13088889999
小九C3.龙, ZZZZ.天,鬼子六 3322152
红河道.换采,风风.EE,我是风儿.狼月,赵五 13877776666
风风.EE,我是风儿.狼月,赵五 15987489999
*/
update BMDH set name =SUBSTRING(BMDH.name,1,len(BMDH.name)-len(substring(REVERSE(BMDH.name),1,CHARINDEX(',',REVERSE(BMDH.name)))))
from QQMA where BMDH.phone=QQMA.phone and reverse(substring(REVERSE(BMDH.name),1,CHARINDEX(',',REVERSE(BMDH.name))-1))=QQMA.name
select * from BMDH order by name desc
/*
name phone
张三,赵一脚D4.鬼人 5566742
小九C3.龙, ZZZZ.天 3322152
小九C3.龙 13088889999
红河道.换采,风风.EE,我是风儿.狼月 13877776666
风风.EE,我是风儿.狼月,赵五 15987489999
*/
drop table BMDH
drop table QQMA