在oracle中分割函数以逗号分隔值自动排序 [英] Split function in oracle to comma separated values with automatic sequence
本文介绍了在oracle中分割函数以逗号分隔值自动排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
例如
$ b $ pre $
SELECT * FROM分割('A,B,C,D',',')
结果应该采用以下格式:
| Id |数据
- ----
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
解决方案
以下是您可以如何创建这样的表格:
SELECT LEVEL AS id,REGEXP_SUBSTR('A,B,C,D','[^,] +',1, LEVEL)as data
FROM dual
CONNECT BY REGEXP_SUBSTR('A,B,C,D','[^,] +',1,LEVEL)不为NULL;
通过一些调整(即替换,
在 [^,]
中有一个变量),你可以编写这样的函数来返回一个表。
Need Split function which will take two parameters, string to split and delimiter to split the string and return a table with columns Id and Data.And how to call Split function which will return a table with columns Id and Data. Id column will contain sequence and data column will contain data of the string. Eg.
SELECT*FROM Split('A,B,C,D',',')
Result Should be in below format:
|Id | Data
-- ----
|1 | A |
|2 | B |
|3 | C |
|4 | D |
解决方案
Here is how you could create such a table:
SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data
FROM dual
CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;
With a little bit of tweaking (i.e., replacing the ,
in [^,]
with a variable) you could write such a function to return a table.
这篇关于在oracle中分割函数以逗号分隔值自动排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文