Oracle 中的逗号分隔值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2627916/
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
Comma Separated values in Oracle
提问by Vabs
I have a column with comma separated values like 6,7,99.3334.
我有一列用逗号分隔的值，如6,7,99.3334.
I need write a PL SQL procedure that will give me these values separately. The Length of the column is 40.
我需要编写一个 PL SQL 过程来分别给我这些值。列的长度为 40。
Can anyone help me with this?
谁能帮我这个？
回答by René Nyffenegger
Something like this maybe?
也许像这样的东西？
with my as (
select '6,7,99.3334' str
from dual
)
select
regexp_substr(my.str,'[^,]+',1,level) part
from my
connect by level <= length(regexp_replace(my.str,'[^,]+')) + 1
;
回答by PaulJ
For a non regex answer...
对于非正则表达式的答案...
SELECT rn
, field
, SUBSTR( ','||field||','
, INSTR( ','||field||',', ',', 1, rn ) + 1
, INSTR( ','||field||',', ',', 1, rn+1 )
- INSTR( ','||field||',', ',', 1, rn )
- 1
) separated_field
FROM ( SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 40 ) -- Length of column
, ( SELECT '6,7,99.3334' field FROM dual ) -- Source column
WHERE rn <= ( LENGTH( field )
- LENGTH( REPLACE( field, ',', NULL ) )
) + 1 -- Number of Commas plus one
回答by DCookie
Here is a view that will split the CSV column into multiple rows:
这是一个将 CSV 列拆分为多行的视图：
CREATE OR REPLACE VIEW your_view AS
SELECT tt.ID, SUBSTR(value, sp, ep-sp) split, other_col1, other_col2...
FROM (SELECT id, value
, INSTR(','||value, ',', 1, L) sp -- 1st posn of substr at this level
, INSTR(value||',', ',', 1, L) ep -- posn of delimiter at this level
FROM tt JOIN (SELECT LEVEL L FROM dual CONNECT BY LEVEL < 20) q -- 20 is max #substrings
ON LENGTH(value)-LENGTH(REPLACE(value,','))+1 >= L
) qq JOIN tt on qq.id = tt.id;
where tt is your table.
哪里是你的桌子。
Works for csv values longer than 1 or null. The CONNECT BY LEVEL < 20 is arbitrary, adjust for your situation.
适用于大于 1 或 null 的 csv 值。CONNECT BY LEVEL < 20 是任意的，根据您的情况进行调整。
To illustrate:
为了显示：
SQL> CREATE TABLE tt (ID INTEGER, c VARCHAR2(20), othercol VARCHAR2(20));
Table created
SQL> INSERT INTO tt VALUES (1, 'a,b,c', 'val1');
1 row inserted
SQL> INSERT INTO tt VALUES (2, 'd,e,f,g', 'val2');
1 row inserted
SQL> INSERT INTO tt VALUES (3, 'a,f', 'val3');
1 row inserted
SQL> INSERT INTO tt VALUES (4,'aa,bbb,cccc', 'val4');
1 row inserted
SQL> CREATE OR REPLACE VIEW myview AS
2 SELECT tt.ID, SUBSTR(c, sp, ep-sp+1) splitval, othercol
3 FROM (SELECT ID
4 , INSTR(','||c,',',1,L) sp, INSTR(c||',',',',1,L)-1 ep
5 FROM tt JOIN (SELECT LEVEL L FROM dual CONNECT BY LEVEL < 20) q
6 ON LENGTH(c)-LENGTH(REPLACE(c,','))+1 >= L
7 ) q JOIN tt ON q.id =tt.id;
View created
SQL> select * from myview order by 1,2;
ID SPLITVAL OTHERCOL
--------------------------------------- -------------------- --------------------
1 a val1
1 b val1
1 c val1
2 d val2
2 e val2
2 f val2
2 g val2
3 a val3
3 f val3
4 aa val4
4 bbb val4
4 cccc val4
12 rows selected
SQL>
回答by Igby Largeman
Here's another approach. This parses your string into a PL/SQL table.
这是另一种方法。这会将您的字符串解析为 PL/SQL 表。
First create a custom type which is an array of numbers:
首先创建一个自定义类型，它是一个数字数组：
CREATE OR REPLACE TYPE number_tab AS TABLE OF NUMBER;
Then create a function which converts a comma-separated list of values to an instance your array type:
然后创建一个函数，将逗号分隔的值列表转换为数组类型的实例：
CREATE OR REPLACE FUNCTION csv_to_number_tab(p_string IN VARCHAR2)
RETURN number_tab AS
l_string LONG DEFAULT p_string || ',';
l_data number_tab := number_tab();
n NUMBER;
BEGIN
LOOP
EXIT WHEN l_string IS NULL;
n := INSTR(l_string, ',');
l_data.EXTEND;
l_data(l_data.COUNT) := TO_NUMBER(LTRIM(RTRIM(SUBSTR(l_string, 1, n - 1))));
l_string := SUBSTR(l_string, n + 1);
END LOOP;
RETURN l_data;
END;
And here's an anonymous block which demonstrates usage:
这是一个演示用法的匿名块：
DECLARE
nt number_tab := number_tab();
i NUMBER := 0;
BEGIN
nt := csv_to_number_tab('1,2.3, 456.7, 89.1234,567890.12345');
FOR i IN 1 .. nt.COUNT LOOP
DBMS_OUTPUT.put_line(i || ' : ' || nt(i));
END LOOP;
END;
Note that there are spaces between some of the values but not others; the function handles it either way.
请注意，某些值之间有空格，但其他值之间没有；该函数以任何一种方式处理它。
回答by David
You haven't said if you want the items in columns or rows. The row solution is quite easy using xml: http://pbarut.blogspot.com/2006/10/binding-list-variable.html
你还没有说你想要列还是行中的项目。使用 xml 的行解决方案非常简单：http: //pbarut.blogspot.com/2006/10/binding-list-variable.html
Basically you convert the string into an xml document then you pull the values out.
基本上，您将字符串转换为 xml 文档，然后将值取出。
