逗号分隔列表 [英] comma Separated List

问题描述

我有一个程序，该程序的参数采用逗号分隔值， 所以当我输入Parameter ='1,0,1'

I have procedure that has parameter that takes comma separated value , so when I enter Parameter = '1,0,1'

我要返回一，零，一"?

I want to return ' one , Zero , One' ?

推荐答案

此查询将列表拆分为数字，将数字转换为单词，然后将其与函数listagg再次连接在一起:

This query splits list into into numbers, converts numbers into words and joins them again together with function listagg:

with t1 as (select '7, 0, 11, 132' col from dual),
     t2 as (select level lvl,to_number(regexp_substr(col,'[^,]+', 1, level)) col 
              from t1 connect by regexp_substr(col, '[^,]+', 1, level) is not null)
select listagg(case 
                 when col=0 then 'zero' 
                 else to_char(to_date(col,'j'), 'jsp') 
               end, 
               ', ') within group (order by lvl) col
  from t2

输出:

COL
-------------------------------------------
seven, zero, eleven, one hundred thirty-two

此解决方案的局限性在于值范围在0到5374484之间(因为5373484是函数to_date的最大值). 如果您需要更高的值，可以在本文中找到提示a>.

The limitation of this solution is that values range is between 0 and 5373484 (because 5373484 is maximum value for function to_date). If you need higher values you can find hints in this article.

这篇关于逗号分隔列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！