关闭

确定字符串第二个大写字母的位置并分拆

102人阅读 评论(0) 收藏 举报

Zygophyllum macropodum Boriss.
Zygophyllum
Zygophyllum macropodum
Zygophyllum potaninii Maxim.
Zygophyllum oxycarpum Popov
Zygophyllum loczyi Kanitz
Zygophyllum pterocarpum Bunge
Zygophyllum sinkiangense Y. X. Liou
上述字段,我想找到各个字符串中第二个大写字母的位置并分拆成两列,如果仅有一个大写的则不分拆,以上分拆结果为:
Zygophyllum macropodum                                              Boriss.
Zygophyllum
Zygophyllum macropodum
Zygophyllum potaninii                                                      Maxim.
Zygophyllum oxycarpum                                                  Popov
Zygophyllum loczyi                                                            Kanitz
Zygophyllum pterocarpum                                               Bunge
Zygophyllum sinkiangense                                              Y. X. Liou


with cte(name) as(

select 'Zygophyllum macropodum Boriss.' union all
select 'Zygophyllum' union all
select 'Zygophyllum macropodum' union all
select 'Zygophyllum potaninii Maxim.' union all
select 'Zygophyllum oxycarpum Popov' union all
select 'Zygophyllum loczyi Kanitz' union all
select 'Zygophyllum pterocarpum Bunge' union all
select 'Zygophyllum sinkiangense Y. X. Liou'
),

tb as(
        select c.*,number,ROW_NUMBER()over(partition by c.name order by number) as rownum 
        from cte c,master..spt_values s 
        where s.type='P' 
             and s.number between 1 and len(c.name) 
             and substring(c.name,number,1)  COLLATE Chinese_PRC_CS_AS  like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'
     )

select name,name vl1,null vl2 
from tb t1 
where t1.rownum=1 
      and not exists(select 1 from tb t2 where t1.name=t2.name and t2.rownum>1)
union all
select name,SUBSTRING(name,1,number-1),SUBSTRING(name,number,len(name)-number+1) 
from tb 
where rownum=2

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:476次
    • 积分:44
    • 等级:
    • 排名:千里之外
    • 原创:4篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档