注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle专业打杂

定会重回巅峰……

 
 
 

日志

 
 

MSSQL字符串截取及游标的应用  

2015-12-01 19:47:21|  分类: MSSQL基础知识 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
需求
将表Patent_Transfer_Info中AF_ASGN字段转换成表Current_Assignee_Info中的三个字段,具体规则如下所示。

Patent_Transfer_Info

Current_Assignee_Info

AF_ASGN

ASGN_NAME

ASGN_ADDRESS

ASGN_ZIP_CODE

申请人:成都永泽药物研究开发有限责任公司  ; 四川省成都市蜀汉路289号锦城苑明园十幢一单元101

成都永泽药物研究开发有限责任公司

四川省成都市蜀汉路289号锦城苑明园十幢一单元101

--

靳桂芝 ; 300222 天津市河西区三水道富江里26-1

靳桂芝

天津市河西区三水道富江里26-1

300222

; 日本东京

保持原内容不变

保持原内容不变

--

诺瓦提斯公司 ; 瑞士巴塞尔

诺瓦提斯公司

瑞士巴塞尔

--

株式会社岛津制作所  ; 日本京都市申请人:卫材药业有限公司  ; 日本东京

株式会社岛津制作所

日本京都市

--

卫材药业有限公司

日本东京

--

申请人:社团法人日本造船研究协会  ; 日本东京;   申请人:日本邮船株式会社  ; 日本东京;   申请人:株式会社商船三井  ; 日本东京;   申请人:三井造船株式会社  ; 日本东京

社团法人日本造船研究协会

日本东京

--

日本邮船株式会社

日本东京

--

株式会社商船三井

日本东京

--

三井造船株式会社

日本东京

--


-----------------------------------------------------------------
实现:
Step1

drop table Current_Assignee_Info_T;
--创建Current_Assignee_Info_T表
create table Current_Assignee_Info_T(id int ,ASGN_NAME varchar(255),ASGN_ADDRESS varchar(255),ASGN_ZIP_COD varchar(255),rn int);

Step2

--字符串分割函数
create function [dbo].[f_split](@c varchar(2000),@split varchar(2)) 
returns @t table(col varchar(max)) 
as 
begin
while(charindex(@split,@c)<>0) 
begin 
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) 
set @c = stuff(@c,1,charindex(@split,@c),'') 
end 
insert @t(col) values (@c) 
return 
end 

Step3

--创建游标来实现数据的清洗
declare get_type3 cursor for
--定义表Patent_Transfer_Info
with Patent_Transfer_Info as (
select 1 as id,'申请人:成都永泽药物研究开发有限责任公司  ; 四川省成都市蜀汉路289号锦城苑明园十幢一单元101号' as AF_ASGN 
union
select 2, '靳桂芝 ; 300222 天津市河西区三水道富江里26-1' 
union
select 3, '无 ; 日本东京'
union
select 4, '诺瓦提斯公司 ; 瑞士巴塞尔'
union
select 5, '株式会社岛津制作所  ; 日本京都市;  申请人:卫材药业有限公司  ; 日本东京'
union
select 6, '申请人:社团法人日本造船研究协会  ; 日本东京;   申请人:日本邮船株式会社  ; 日本东京;   申请人:株式会社商船三井  ; 日本东京;   申请人:三井造船株式会社  ; 日本东京'
)
select id,AF_ASGN
from Patent_Transfer_Info 
--where charindex(',',AF_ASGN)<>0;

--打开游标
open get_type3
DECLARE @n_id int
DECLARE @n_ab varchar(255)
-- 将游标中取出的数据赋值给@n_id,@n_ab
fetch next from get_type3
into @n_id,@n_ab
while (@@FETCH_STATUS = 0 )
begin
insert into Current_Assignee_Info_T (id ,ASGN_NAME ,ASGN_ADDRESS ,ASGN_ZIP_COD,rn)
--将数据插入到表Current_Assignee_Info_T中
select max(ID) as ID,max(ASGN_NAME) as ASGN_NAME,max(ASGN_ADDRESS) as ASGN_ADDRESS,MAX(ASGN_ZIP_CODE) as ASGN_ZIP_CODE,rn
--select ID,ASGN_NAME,ASGN_ADDRESS,ASGN_ZIP_CODE,rn
from 
(
select ID,case when rn%2 = 1 then  SUBSTRING(col,CHARINDEX(':',col)+1,LEN(col)) end as ASGN_NAME,
case when rn%2 = 0 and ltrim(col) not like '[0-9][0-9][0-9][0-9][0-9][0-9]%' then col 
--col字符串中不含邮编时直接赋值给addr
when rn%2 = 0 and ltrim(col) like '[0-9][0-9][0-9][0-9][0-9][0-9]%' 
then SUBSTRING(replace(col,' ',''),7,LEN(col)) end as ASGN_ADDRESS
--col字符串中含有邮编时从第7位起截取字符串赋值给addr
,case when rn%2 = 0 and ltrim(col) like '[0-9][0-9][0-9][0-9][0-9][0-9]%' 
then SUBSTRING(replace(col,' ',''),1,6) end as ASGN_ZIP_CODE
--col字符串中含有邮编时从第1位起到第6位截取字符串赋值给pnt
,floor((rn+1)/2) as rn
--对 rn+1进行向上取整,以便数据分组合并
from  
(select @n_id as id ,col
,ROW_NUMBER() over (partition by @n_id order by @n_id) rn
from dbo.f_split(@n_ab,';')) xbx
-- 利用f_split函数对@n_ab字符串分割,并用rn进行编号
)abc
group by rn
fetch next from get_type3
into @n_id,@n_ab
end
--关闭游标
close get_type3
--释放游标
deallocate get_type3

Step4 数据验证
原数据
MSSQL字符串截取应用 - 卢大圣 - 龙城醉梦
 
中间数据
MSSQL字符串截取应用 - 卢大圣 - 龙城醉梦

验证插入到表Current_Assignee_Info_T中的数据是否符合规则
MSSQL字符串截取应用 - 卢大圣 - 龙城醉梦
 
 
  评论这张
 
阅读(33)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017