oracle数组转换字符串函数,Oracle的字符串转数组以及数组转字符串

字符串转数组：

(SELECT REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL, 'i') AS STR

FROM DUAL

CONNECT BY LEVEL <=

LENGTH('34,56,-23') - LENGTH(REGEXP_REPLACE('34,56,-23', ',', '')) + 1)

针对上面语法进行解析(转载自https://blog.csdn.net/u012643122/article/details/78932709)：

regexp_substr详解：

regexp_substr函数格式如下：

function regexp_substr(String, pattern, position, occurrence, modifier)

__srcstr ：要操作的字符串

__pattern ：正则匹配规则字符串

__position ：起始位置，1表示从第一个字符开始匹配

__occurrence ：标识第几个匹配组，默认为1

__modifier ：模式('i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’。)

level解释：

参数level是oracle的关键字，是一个伪列，伪劣的数据是oracle自动生成的，一般就是1、2、3等等这样。

一般配合connect by一起使用。

connect by解释：

connect by相当于查询条件，在查询字段中使用了level伪列时，必须使用connect by作为替代where来筛选伪列的值。

数组转字符串

select wmsys.wm_concat(cityname) from sys_city

where provinceid='29'

工作中的例子：sql如下

select

zyfx.zyfxmc as majorName,

zyfx.zyfxdm as majorCode,

nj.nj as gradeMajorYear,

nj.ZDXYNX as gradeMajorShortStudyLength,

nj.ZCXYNX as gradeMajorLongStudyLength,

nj.XZ as gradeMajorStudyLength,

gbzy.gbzymc as standardMajorName,

gbzy.gbzydm as standardMajorCode,

zyfa.FAJJ as schemeIntroduce,

(

select

FABBMC

from T_PYFA_BBXX

where SYNF = (

select

NJ

from T_GGZY_NJZYFX@hxsj

where njzyfx_id = #{gradeMajorId,jdbcType=VARCHAR}

)

and XSLBM = (

select

XSLBM

from T_GGZY_NJZYFX@hxsj

where njzyfx_id = #{gradeMajorId,jdbcType=VARCHAR}

)

)as schemeVersionName,

(

select

wm_concat(t.DMMC)

from T_DMK_DMZ@Hxsj t

where t.DMBH='XWSYMLM'

and t.DMH in

(

SELECT

REGEXP_SUBSTR(nj.XWSYMLM, '[^,]+', 1, LEVEL, 'i') AS STR

FROM DUAL

CONNECT BY LEVEL <=

LENGTH(nj.XWSYMLM) - LENGTH(REGEXP_REPLACE(nj.XWSYMLM, ',', '')) + 1)

) as gradeMajorDegreeCategoryName

from T_GGZY_NJZYFX@hxsj nj

left join T_GGZY_ZYFX@hxsj zyfx

on zyfx.ZYFX_ID = nj.ZYFX_ID

left join T_GGZY_GBZY@hxsj gbzy

on gbzy.GBZY_ID = zyfx.GBZY_ID

left join T_PYFA_ZYFAJBXX zyfa

on zyfa.njzyfx_id = nj.njzyfx_id

where nj.njzyfx_id = #{gradeMajorId,jdbcType=VARCHAR}