将 oracle 中的函数拆分为具有自动序列的逗号分隔值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28677070/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split function in oracle to comma separated values with automatic sequence
提问by AKBAR ALI
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.
需要 Split 函数，它将采用两个参数，要拆分的字符串和用于拆分字符串的分隔符，并返回一个包含 Id 和 Data 列的表。以及如何调用 Split 函数，该函数将返回一个包含 Id 和 Data 列的表。Id 列将包含序列，数据列将包含字符串的数据。例如。
SELECT*FROM Split('A,B,C,D',',')
Result Should be in below format:
结果应采用以下格式：
|Id | Data
-- ----
|1 | A |
|2 | B |
|3 | C |
|4 | D |
回答by David Faber
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.
稍加调整（即，用变量替换,in [^,]），您就可以编写这样一个函数来返回一个表。
回答by Lalit Kumar B
There are multiple options. See Split single comma delimited string into rows in Oracle
有多种选择。请参阅将单个逗号分隔的字符串拆分为 Oracle 中的行
You just need to add LEVELin the select list as a column, to get the sequencenumber to each row returned. Or, ROWNUMwould also suffice.
您只需要在选择列表中添加LEVEL作为列，即可获取返回的每一行的序列号。或者，ROWNUM也足够了。
Using any of the below SQLs, you could include them into a FUNCTION.
使用以下任何 SQL，您都可以将它们包含到FUNCTION 中。
INSTRin CONNECT BYclause:
INSTR在CONNECT BY子句：
SQL> WITH DATA AS 2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual 3 ) 4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 5 FROM DATA 6 CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0 7 / STR ---------------------------------------- word1 word2 word3 word4 word5 word6 6 rows selected. SQL>
REGEXP_SUBSTRin CONNECT BYclause:
REGEXP_SUBSTR在CONNECT BY子句：
SQL> WITH DATA AS 2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual 3 ) 4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 5 FROM DATA 6 CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL 7 / STR ---------------------------------------- word1 word2 word3 word4 word5 word6 6 rows selected. SQL>
REGEXP_COUNTin CONNECT BYclause:
REGEXP_COUNT在CONNECT BY子句：
SQL> WITH DATA AS 2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual 3 ) 4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 5 FROM DATA 6 CONNECT BY LEVEL
Using XMLTABLE
使用XMLTABLE
SQL> WITH DATA AS
2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
3 )
4 SELECT trim(COLUMN_VALUE) str
5 FROM DATA, xmltable(('"' || REPLACE(str, ',', '","') || '"'))
6 /
STR
------------------------------------------------------------------------
word1
word2
word3
word4
word5
word6
6 rows selected.
SQL>
Using MODELclause:
使用MODEL子句：
SQL> WITH t AS 2 ( 3 SELECT 'word1, word2, word3, word4, word5, word6' str 4 FROM dual ) , 5 model_param AS 6 ( 7 SELECT str AS orig_str , 8 ',' 9 || str 10 || ',' AS mod_str , 11 1 AS start_pos , 12 Length(str) AS end_pos , 13 (Length(str) - Length(Replace(str, ','))) + 1 AS element_count , 14 0 AS element_no , 15 ROWNUM AS rn 16 FROM t ) 17 SELECT trim(Substr(mod_str, start_pos, end_pos-start_pos)) str 18 FROM ( 19 SELECT * 20 FROM model_param MODEL PARTITION BY (rn, orig_str, mod_str) 21 DIMENSION BY (element_no) 22 MEASURES (start_pos, end_pos, element_count) 23 RULES ITERATE (2000) 24 UNTIL (ITERATION_NUMBER+1 = element_count[0]) 25 ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1, 26 end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1) ) ) 27 WHERE element_no != 0 28 ORDER BY mod_str , 29 element_no 30 / STR ------------------------------------------ word1 word2 word3 word4 word5 word6 6 rows selected. SQL>
You could also use DBMS_UTILITYpackage provided by Oracle. It provides various utility subprograms. One such useful utility is COMMA_TO_TABLE procedure, which converts a comma-delimited list of names into a PL/SQL table of names.
您也可以使用Oracle 提供的DBMS_UTILITY包。它提供了各种实用程序子程序。一个这样有用的实用程序是COMMA_TO_TABLE 过程，它将逗号分隔的名称列表转换为 PL/SQL 名称表。
回答by MT0
Oracle Setup:
甲骨文设置：
CREATE OR REPLACE FUNCTION split_String(
i_str IN VARCHAR2,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
AS
p_result SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
p_start NUMBER(5) := 1;
p_end NUMBER(5);
c_len CONSTANT NUMBER(5) := LENGTH( i_str );
c_ld CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
p_start := p_end + c_ld;
p_end := INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
END IF;
END IF;
RETURN p_result;
END;
/
Query
询问
SELECT ROWNUM AS ID,
COLUMN_VALUE AS Data
FROM TABLE( split_String( 'A,B,C,D' ) );
Output:
输出：
ID DATA
-- ----
1 A
2 B
3 C
4 D
回答by mmmmmpie
If you need a function try this.
First we'll create a type:
如果你需要一个功能试试这个。
首先，我们将创建一个类型：
CREATE OR REPLACE TYPE T_TABLE IS OBJECT
(
Field1 int
, Field2 VARCHAR(25)
);
CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
/
Then we'll create the function:
然后我们将创建函数：
CREATE OR REPLACE FUNCTION TEST_RETURN_TABLE
RETURN T_TABLE_COLL
IS
l_res_coll T_TABLE_COLL;
l_index number;
BEGIN
l_res_coll := T_TABLE_COLL();
FOR i IN (
WITH TAB AS
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
UNION
SELECT '1002' ID, 'D,E,F' STR FROM DUAL
UNION
SELECT '1003' ID, 'C,E,G' STR FROM DUAL
)
SELECT id,
SUBSTR(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
FROM
( SELECT ',' || STR || ',' AS STR, id FROM TAB
),
( SELECT level AS lvl FROM dual CONNECT BY level <= 100
)
WHERE lvl <= LENGTH(STR) - LENGTH(REPLACE(STR, ',')) - 1
ORDER BY ID, NAME)
LOOP
IF i.ID = 1001 THEN
l_res_coll.extend;
l_index := l_res_coll.count;
l_res_coll(l_index):= T_TABLE(i.ID, i.name);
END IF;
END LOOP;
RETURN l_res_coll;
END;
/
Now we can select from it:
现在我们可以从中选择：
select * from table(TEST_RETURN_TABLE());
Output:
输出：
SQL> select * from table(TEST_RETURN_TABLE());
FIELD1 FIELD2
---------- -------------------------
1001 A
1001 B
1001 C
1001 D
1001 E
1001 F
6 rows selected.
Obviously you'd need to replace the WITH TAB AS...bit with where you would be getting your actual data from.
CreditCredit
回答by Alexander Mchedlishvili
Use this 'Split' function:
使用此“拆分”功能：
CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sys_refcursor is
v_res sys_refcursor;
begin
open v_res for
WITH TAB AS
(SELECT p_str STR FROM DUAL)
select substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
from
( select ',' || STR || ',' as STR from TAB ),
( select level as lvl from dual connect by level <= 100 )
where lvl <= length(STR) - length(replace(STR, ',')) - 1;
return v_res;
end;
You can't use this function in select statement like you described in question, but I hope you will find it still useful.
你不能像你所描述的那样在 select 语句中使用这个函数，但我希望你会发现它仍然有用。
EDIT: Here are steps you need to do. 1. Create Object: create or replace type empy_type as object(value varchar2(512)) 2. Create Type: create or replace type t_empty_type as table of empy_type 3. Create Function:
编辑：以下是您需要执行的步骤。1. 创建对象：创建或替换类型 empy_type 作为对象(value varchar2(512)) 2. 创建类型：创建或替换类型 t_empty_type 作为 empy_type 的表 3. 创建函数：
CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sms.t_empty_type is
v_emptype t_empty_type := t_empty_type();
v_cnt number := 0;
v_res sys_refcursor;
v_value nvarchar2(128);
begin
open v_res for
WITH TAB AS
(SELECT p_str STR FROM DUAL)
select substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
from
( select ',' || STR || ',' as STR from TAB ),
( select level as lvl from dual connect by level <= 100 )
where lvl <= length(STR) - length(replace(STR, ',')) - 1;
loop
fetch v_res into v_value;
exit when v_res%NOTFOUND;
v_emptype.extend;
v_cnt := v_cnt + 1;
v_emptype(v_cnt) := empty_type(v_value);
end loop;
close v_res;
return v_emptype;
end;
Then just call like this:
然后就这样调用：
SELECT * FROM (TABLE(split('a,b,c,d,g')))
回答by Pavan Kumar Rao
This function returns the nth part of input string MYSTRING. Second input parameter is separator ie., SEPARATOR_OF_SUBSTR and the third parameter is Nth Part which is required.
此函数返回输入字符串 MYSTRING 的第 n 部分。第二个输入参数是分隔符，即 SEPARATOR_OF_SUBSTR，第三个参数是需要的第 N 部分。
Note: MYSTRING should end with the separator.
注意：MYSTRING 应以分隔符结尾。
create or replace FUNCTION PK_GET_NTH_PART(MYSTRING VARCHAR2,SEPARATOR_OF_SUBSTR VARCHAR2,NTH_PART NUMBER)
RETURN VARCHAR2
IS
NTH_SUBSTR VARCHAR2(500);
POS1 NUMBER(4);
POS2 NUMBER(4);
BEGIN
IF NTH_PART=1 THEN
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, 1) INTO POS1 FROM DUAL;
SELECT SUBSTR(MYSTRING,0,POS1-1) INTO NTH_SUBSTR FROM DUAL;
ELSE
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART-1) INTO POS1 FROM DUAL;
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART) INTO POS2 FROM DUAL;
SELECT SUBSTR(MYSTRING,POS1+1,(POS2-POS1-1)) INTO NTH_SUBSTR FROM DUAL;
END IF;
RETURN NTH_SUBSTR;
END;
Hope this helps some body, you can use this function like this in a loop to get all the values separated:
希望这对某些人有所帮助，您可以在循环中像这样使用此函数来分离所有值：
SELECT REGEXP_COUNT(MYSTRING, '~', 1, 'i') INTO NO_OF_RECORDS FROM DUAL;
WHILE NO_OF_RECORDS>0
LOOP
PK_RECORD :=PK_GET_NTH_PART(MYSTRING,'~',NO_OF_RECORDS);
-- do some thing
NO_OF_RECORDS :=NO_OF_RECORDS-1;
END LOOP;
Here NO_OF_RECORDS,PK_RECORD are temp variables.
这里 NO_OF_RECORDS,PK_RECORD 是临时变量。
Hope this helps.
希望这可以帮助。
回答by Muhammad Tazeem
Best Query For comma separated in This Query we Convert Rows To Column ...
最佳查询对于在此查询中分隔的逗号，我们将行转换为列...
SELECT listagg(BL_PRODUCT_DESC, ', ') within
group( order by BL_PRODUCT_DESC) PROD
FROM GET_PRODUCT
-- WHERE BL_PRODUCT_DESC LIKE ('%WASH%')
WHERE Get_Product_Type_Id = 6000000000007
回答by Ifthakharul Islam Dollar
Try like below
尝试如下
select
split.field(column_name,1,',','"') name1,
split.field(column_name,2,',','"') name2
from table_name
