将 oracle 中的函数拆分为具有自动序列的逗号分隔值
需要 Split 函数，该函数将采用两个参数，要拆分的字符串和用于拆分字符串的分隔符，并返回一个包含 Id 和 Data 列的表.以及如何调用 Split 函数，该函数将返回一个包含 Id 和 Data 列的表.Id 列将包含序列，数据列将包含字符串的数据.例如.
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',',')
结果应采用以下格式:
|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.
相关文章