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

最新推荐文章于 2022-10-18 16:44:50 发布

转载最新推荐文章于 2022-10-18 16:44:50 发布 · 1k 阅读

本文介绍了一种处理植物名称数据的方法，通过SQL查询将含有特定格式的植物名称字符串进行分解，以便更好地管理和分析这些数据。文章展示了如何使用SQL来定位和拆分字符串中的关键部分。

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