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

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

• 本文已收录于以下专栏：

举报原因： 您举报文章：确定字符串第二个大写字母的位置并分拆 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)