Как преобразовать строку, разделенную запятыми, в массив?
У меня есть ввод '1,2,3'
, и мне нужно преобразовать его в массив.
Как преобразовать строку, разделенную запятыми, в массив?
У меня есть ввод '1,2,3'
, и мне нужно преобразовать его в массив.
Oracle предоставляет встроенную функцию DBMS_UTILITY.COMMA_TO_TABLE.
К сожалению, этот не работает с числами:
SQL> declare
2 l_input varchar2(4000) := '1,2,3';
3 l_count binary_integer;
4 l_array dbms_utility.lname_array;
5 begin
6 dbms_utility.comma_to_table
7 ( list => l_input
8 , tablen => l_count
9 , tab => l_array
10 );
11 dbms_output.put_line(l_count);
12 for i in 1 .. l_count
13 loop
14 dbms_output.put_line
15 ( 'Element ' || to_char(i) ||
16 ' of array contains: ' ||
17 l_array(i)
18 );
19 end loop;
20 end;
21 /
declare
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 132
ORA-06512: at "SYS.DBMS_UTILITY", line 164
ORA-06512: at "SYS.DBMS_UTILITY", line 218
ORA-06512: at line 6
Но с небольшим трюком, чтобы добавить к элементам префикс «x», это работает:
SQL> declare
2 l_input varchar2(4000) := '1,2,3';
3 l_count binary_integer;
4 l_array dbms_utility.lname_array;
5 begin
6 dbms_utility.comma_to_table
7 ( list => regexp_replace(l_input,'(^|,)','\1x')
8 , tablen => l_count
9 , tab => l_array
10 );
11 dbms_output.put_line(l_count);
12 for i in 1 .. l_count
13 loop
14 dbms_output.put_line
15 ( 'Element ' || to_char(i) ||
16 ' of array contains: ' ||
17 substr(l_array(i),2)
18 );
19 end loop;
20 end;
21 /
3
Element 1 of array contains: 1
Element 2 of array contains: 2
Element 3 of array contains: 3
PL/SQL procedure successfully completed.
С уважением, Роб.
COMMA_TO_TABLE
(и обратные TABLE_TO_COMMA
) не написаны для этой цели! ... Они написаны в первую очередь для использования внутри репликации внутри Oracle и анализируют ИДЕНТИФИКАТОРЫ, а не строки, и поэтому должны быть допустимыми именами объектов Oracle.
- person Terrible Tadpole; 29.03.2016
'8.5.17.1,8.5.17.2'
Ошибка ORA-20001: comma-separated list invalid near x8.5.
Можете ли вы помочь решить эту проблему???
- person Venkatesh; 09.05.2017
вот еще более простой вариант
select to_number(column_value) as IDs from xmltable('1,2,3,4,5');
FOR i IN (SELECT to_number(column_value) as ID FROM xmltable('1,2,3,4,5')) LOOP... END LOOP;
просто превосходно, спасибо!
- person mike; 01.08.2018
ORA-01460: unimplemented or unreasonable conversion requested
для строки длиной >4000 байт.
- person dinvlad; 26.02.2020
Мы никогда не исчерпаем возможности сделать одно и то же по-другому, верно? Недавно я обнаружил, что это очень удобно:
DECLARE
BAR VARCHAR2 (200) := '1,2,3';
BEGIN
FOR FOO IN ( SELECT REGEXP_SUBSTR (BAR,
'[^,]+',
1,
LEVEL)
TXT
FROM DUAL
CONNECT BY REGEXP_SUBSTR (BAR,
'[^,]+',
1,
LEVEL)
IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE (FOO.TXT);
END LOOP;
END;
Выходы:
1
2
3
LEVEL
) одинаково. Если ваши списки со временем будут расти, это представляет риск масштабируемости.
- person Terrible Tadpole; 29.03.2016
Я знаю, что Stack Overflow не одобряет вставку URL-адресов без объяснений, но на этой конкретной странице есть несколько действительно хороших вариантов:
http://www.oratechinfo.co.uk/delimited_lists_to_collections.html
Мне особенно нравится этот, который преобразует список с разделителями во временную таблицу, к которой вы можете запускать запросы:
/* Create the output TYPE, here using a VARCHAR2(100) nested table type */
SQL> CREATE TYPE test_type AS TABLE OF VARCHAR2(100);
2 /
Type created.
/* Now, create the function.*/
SQL> CREATE OR REPLACE FUNCTION f_convert(p_list IN VARCHAR2)
2 RETURN test_type
3 AS
4 l_string VARCHAR2(32767) := p_list || ',';
5 l_comma_index PLS_INTEGER;
6 l_index PLS_INTEGER := 1;
7 l_tab test_type := test_type();
8 BEGIN
9 LOOP
10 l_comma_index := INSTR(l_string, ',', l_index);
11 EXIT WHEN l_comma_index = 0;
12 l_tab.EXTEND;
13 l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
14 l_index := l_comma_index + 1;
15 END LOOP;
16 RETURN l_tab;
17 END f_convert;
18 /
Function created.
/* Prove it works */
SQL> SELECT * FROM TABLE(f_convert('AAA,BBB,CCC,D'));
COLUMN_VALUE
--------------------------------------------------------------------------------
AAA
BBB
CCC
D
4 rows selected.
Простой код
create or replace function get_token(text_is varchar2, token_in number, delim_is varchar2 := ';') return varchar2 is text_ls varchar2(2000); spos_ln number; epos _ln number; begin text_ls := delim_is || text_is || rpad(delim_is, token_in, delim_is); spos_ln := instr(text_ls, delim_is, 1, token_in); epos_ln := instr(text_ls, delim_is, 1, token_in+1); return substr(text_ls, spos_ln+1, epos_ln-spos_ln-1); end get_token;
Да, очень расстраивает то, что dbms_utility.comma_to_table поддерживает только списки, разделенные запятыми, и только тогда, когда элементы в списке являются допустимыми, идентифицирует PL/SQL (поэтому числа вызывают ошибку).
Я создал общий пакет синтаксического анализа, который будет делать то, что вам нужно (вставлено ниже). Это часть моего файла «demo.zip», репозитория из более чем 2000 файлов, поддерживающих мои учебные материалы, все они доступны на PL/SQL Obsession: www.toadworld.com/SF.
С уважением, Steven Feuerstein www.plsqlchallenge.com (ежедневная викторина по PL/SQL)
CREATE OR REPLACE PACKAGE parse
/*
Generalized delimited string parsing package
Author: Steven Feuerstein, [email protected]
Latest version always available on PL/SQL Obsession:
www.ToadWorld.com/SF
Click on "Trainings, Seminars and Presentations" and
then download the demo.zip file.
Modification History
Date Change
10-APR-2009 Add support for nested list variations
Notes:
* This package does not validate correct use of delimiters.
It assumes valid construction of lists.
* Import the Q##PARSE.qut file into an installation of
Quest Code Tester 1.8.3 or higher in order to run
the regression test for this package.
*/
IS
SUBTYPE maxvarchar2_t IS VARCHAR2 (32767);
/*
Each of the collection types below correspond to (are returned by)
one of the parse functions.
items_tt - a simple list of strings
nested_items_tt - a list of lists of strings
named_nested_items_tt - a list of named lists of strings
This last type also demonstrates the power and elegance of string-indexed
collections. The name of the list of elements is the index value for
the "outer" collection.
*/
TYPE items_tt IS TABLE OF maxvarchar2_t
INDEX BY PLS_INTEGER;
TYPE nested_items_tt IS TABLE OF items_tt
INDEX BY PLS_INTEGER;
TYPE named_nested_items_tt IS TABLE OF items_tt
INDEX BY maxvarchar2_t;
/*
Parse lists with a single delimiter.
Example: a,b,c,d
Here is an example of using this function:
DECLARE
l_list parse.items_tt;
BEGIN
l_list := parse.string_to_list ('a,b,c,d', ',');
END;
*/
FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2)
RETURN items_tt;
/*
Parse lists with nested delimiters.
Example: a,b,c,d|1,2,3|x,y,z
Here is an example of using this function:
DECLARE
l_list parse.nested_items_tt;
BEGIN
l_list := parse.string_to_list ('a,b,c,d|1,2,3,4', '|', ',');
END;
*/
FUNCTION string_to_list (string_in IN VARCHAR2
, outer_delim_in IN VARCHAR2
, inner_delim_in IN VARCHAR2
)
RETURN nested_items_tt;
/*
Parse named lists with nested delimiters.
Example: letters:a,b,c,d|numbers:1,2,3|names:steven,george
Here is an example of using this function:
DECLARE
l_list parse.named_nested_items_tt;
BEGIN
l_list := parse.string_to_list ('letters:a,b,c,d|numbers:1,2,3,4', '|', ':', ',');
END;
*/
FUNCTION string_to_list (string_in IN VARCHAR2
, outer_delim_in IN VARCHAR2
, name_delim_in IN VARCHAR2
, inner_delim_in IN VARCHAR2
)
RETURN named_nested_items_tt;
PROCEDURE display_list (string_in IN VARCHAR2
, delim_in IN VARCHAR2:= ','
);
PROCEDURE display_list (string_in IN VARCHAR2
, outer_delim_in IN VARCHAR2
, inner_delim_in IN VARCHAR2
);
PROCEDURE display_list (string_in IN VARCHAR2
, outer_delim_in IN VARCHAR2
, name_delim_in IN VARCHAR2
, inner_delim_in IN VARCHAR2
);
PROCEDURE show_variations;
/* Helper function for automated testing */
FUNCTION nested_eq (list1_in IN items_tt
, list2_in IN items_tt
, nulls_eq_in IN BOOLEAN
)
RETURN BOOLEAN;
END parse;
/
CREATE OR REPLACE PACKAGE BODY parse
IS
FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2)
RETURN items_tt
IS
c_end_of_list CONSTANT PLS_INTEGER := -99;
l_item maxvarchar2_t;
l_startloc PLS_INTEGER := 1;
items_out items_tt;
PROCEDURE add_item (item_in IN VARCHAR2)
IS
BEGIN
IF item_in = delim_in
THEN
/* We don't put delimiters into the collection. */
NULL;
ELSE
items_out (items_out.COUNT + 1) := item_in;
END IF;
END;
PROCEDURE get_next_item (string_in IN VARCHAR2
, start_location_io IN OUT PLS_INTEGER
, item_out OUT VARCHAR2
)
IS
l_loc PLS_INTEGER;
BEGIN
l_loc := INSTR (string_in, delim_in, start_location_io);
IF l_loc = start_location_io
THEN
/* A null item (two consecutive delimiters) */
item_out := NULL;
ELSIF l_loc = 0
THEN
/* We are at the last item in the list. */
item_out := SUBSTR (string_in, start_location_io);
ELSE
/* Extract the element between the two positions. */
item_out :=
SUBSTR (string_in
, start_location_io
, l_loc - start_location_io
);
END IF;
IF l_loc = 0
THEN
/* If the delimiter was not found, send back indication
that we are at the end of the list. */
start_location_io := c_end_of_list;
ELSE
/* Move the starting point for the INSTR search forward. */
start_location_io := l_loc + 1;
END IF;
END get_next_item;
BEGIN
IF string_in IS NULL OR delim_in IS NULL
THEN
/* Nothing to do except pass back the empty collection. */
NULL;
ELSE
LOOP
get_next_item (string_in, l_startloc, l_item);
add_item (l_item);
EXIT WHEN l_startloc = c_end_of_list;
END LOOP;
END IF;
RETURN items_out;
END string_to_list;
FUNCTION string_to_list (string_in IN VARCHAR2
, outer_delim_in IN VARCHAR2
, inner_delim_in IN VARCHAR2
)
RETURN nested_items_tt
IS
l_elements items_tt;
l_return nested_items_tt;
BEGIN
/* Separate out the different lists. */
l_elements := string_to_list (string_in, outer_delim_in);
/* For each list, parse out the separate items
and add them to the end of the list of items
for that list. */
FOR indx IN 1 .. l_elements.COUNT
LOOP
l_return (l_return.COUNT + 1) :=
string_to_list (l_elements (indx), inner_delim_in);
END LOOP;
RETURN l_return;
END string_to_list;
FUNCTION string_to_list (string_in IN VARCHAR2
, outer_delim_in IN VARCHAR2
, name_delim_in IN VARCHAR2
, inner_delim_in IN VARCHAR2
)
RETURN named_nested_items_tt
IS
c_name_position constant pls_integer := 1;
c_items_position constant pls_integer := 2;
l_elements items_tt;
l_name_and_values items_tt;
l_return named_nested_items_tt;
BEGIN
/* Separate out the different lists. */
l_elements := string_to_list (string_in, outer_delim_in);
FOR indx IN 1 .. l_elements.COUNT
LOOP
/* Extract the name and the list of items that go with
the name. This collection always has just two elements:
index 1 - the name
index 2 - the list of values
*/
l_name_and_values :=
string_to_list (l_elements (indx), name_delim_in);
/*
Use the name as the index value for this list.
*/
l_return (l_name_and_values (c_name_position)) :=
string_to_list (l_name_and_values (c_items_position), inner_delim_in);
END LOOP;
RETURN l_return;
END string_to_list;
PROCEDURE display_list (string_in IN VARCHAR2
, delim_in IN VARCHAR2:= ','
)
IS
l_items items_tt;
BEGIN
DBMS_OUTPUT.put_line (
'Parse "' || string_in || '" using "' || delim_in || '"'
);
l_items := string_to_list (string_in, delim_in);
FOR indx IN 1 .. l_items.COUNT
LOOP
DBMS_OUTPUT.put_line ('> ' || indx || ' = ' || l_items (indx));
END LOOP;
END display_list;
PROCEDURE display_list (string_in IN VARCHAR2
, outer_delim_in IN VARCHAR2
, inner_delim_in IN VARCHAR2
)
IS
l_items nested_items_tt;
BEGIN
DBMS_OUTPUT.put_line( 'Parse "'
|| string_in
|| '" using "'
|| outer_delim_in
|| '-'
|| inner_delim_in
|| '"');
l_items := string_to_list (string_in, outer_delim_in, inner_delim_in);
FOR outer_index IN 1 .. l_items.COUNT
LOOP
DBMS_OUTPUT.put_line( 'List '
|| outer_index
|| ' contains '
|| l_items (outer_index).COUNT
|| ' elements');
FOR inner_index IN 1 .. l_items (outer_index).COUNT
LOOP
DBMS_OUTPUT.put_line( '> Value '
|| inner_index
|| ' = '
|| l_items (outer_index) (inner_index));
END LOOP;
END LOOP;
END display_list;
PROCEDURE display_list (string_in IN VARCHAR2
, outer_delim_in IN VARCHAR2
, name_delim_in IN VARCHAR2
, inner_delim_in IN VARCHAR2
)
IS
l_items named_nested_items_tt;
l_index maxvarchar2_t;
BEGIN
DBMS_OUTPUT.put_line( 'Parse "'
|| string_in
|| '" using "'
|| outer_delim_in
|| '-'
|| name_delim_in
|| '-'
|| inner_delim_in
|| '"');
l_items :=
string_to_list (string_in
, outer_delim_in
, name_delim_in
, inner_delim_in
);
l_index := l_items.FIRST;
WHILE (l_index IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line( 'List "'
|| l_index
|| '" contains '
|| l_items (l_index).COUNT
|| ' elements');
FOR inner_index IN 1 .. l_items (l_index).COUNT
LOOP
DBMS_OUTPUT.put_line( '> Value '
|| inner_index
|| ' = '
|| l_items (l_index) (inner_index));
END LOOP;
l_index := l_items.NEXT (l_index);
END LOOP;
END display_list;
PROCEDURE show_variations
IS
PROCEDURE show_header (title_in IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
DBMS_OUTPUT.put_line (title_in);
DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
END show_header;
BEGIN
show_header ('Single Delimiter Lists');
display_list ('a,b,c');
display_list ('a;b;c', ';');
display_list ('a,,b,c');
display_list (',,b,c,,');
show_header ('Nested Lists');
display_list ('a,b,c,d|1,2,3|x,y,z', '|', ',');
show_header ('Named, Nested Lists');
display_list ('letters:a,b,c,d|numbers:1,2,3|names:steven,george'
, '|'
, ':'
, ','
);
END;
FUNCTION nested_eq (list1_in IN items_tt
, list2_in IN items_tt
, nulls_eq_in IN BOOLEAN
)
RETURN BOOLEAN
IS
l_return BOOLEAN := list1_in.COUNT = list2_in.COUNT;
l_index PLS_INTEGER := 1;
BEGIN
WHILE (l_return AND l_index IS NOT NULL)
LOOP
l_return := list1_in (l_index) = list2_in (l_index);
l_index := list1_in.NEXT (l_index);
END LOOP;
RETURN l_return;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN FALSE;
END nested_eq;
END;
/
Использование конвейерной табличной функции а>:
SQL> CREATE OR REPLACE TYPE test_type
2 AS
3 TABLE OF VARCHAR2(100)
4 /
Type created.
SQL> CREATE OR REPLACE FUNCTION comma_to_table(
2 p_list IN VARCHAR2)
3 RETURN test_type PIPELINED
4 AS
5 l_string LONG := p_list || ',';
6 l_comma_index PLS_INTEGER;
7 l_index PLS_INTEGER := 1;
8 BEGIN
9 LOOP
10 l_comma_index := INSTR(l_string, ',', l_index);
11 EXIT
12 WHEN l_comma_index = 0;
13 PIPE ROW ( TRIM(SUBSTR(l_string, l_index, l_comma_index - l_index)));
14 l_index := l_comma_index + 1;
15 END LOOP;
16 RETURN;
17 END comma_to_table;
18 /
Function created.
Посмотрим вывод:
SQL> SELECT *
2 FROM TABLE(comma_to_table('12 3,456,,,,,abc,def'))
3 /
COLUMN_VALUE
------------------------------------------------------------------------------
12 3
456
abc
def
8 rows selected.
SQL>
Быстрый поиск в моем BBDD привел меня к функции под названием split:
create or replace function split
(
p_list varchar2,
p_del varchar2 := ','
)
return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;AA
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
Не знаю, пригодится ли он, но мы нашли его здесь...
Я искал аналогичное решение, в котором у меня были многобайтовые символы (дефис, пробел, подчеркивание) в списках, разделенных запятыми. Так что dbms_utility.comma_to_table
у меня не сработало.
declare
curr_val varchar2 (255 byte);
input_str varchar2 (255 byte);
remaining_str varchar2 (255 byte);
begin
remaining_str := input_str || ',dummy'; -- this value won't output
while (regexp_like (remaining_str, '.+,.+'))
loop
curr_val := substr (remaining_str, 1, instr (remaining_str, ',') - 1);
remaining_str = substr (remaining_str, instr (remaining_str, ',') + 1);
dbms_output.put_line (curr_val);
end loop;
end;
Это не экспертный ответ, поэтому я надеюсь, что кто-то улучшит этот ответ.
Другая возможность:
create or replace FUNCTION getNth (
input varchar2,
nth number
) RETURN varchar2 AS
nthVal varchar2(80);
BEGIN
with candidates (s,e,n) as (
select 1, instr(input,',',1), 1 from dual
union all
select e+1, instr(input,',',e+1), n+1
from candidates where e > 0)
select substr(input,s,case when e > 0 then e-s else length(input) end)
into nthVal
from candidates where n=nth;
return nthVal;
END getNth;
Это слишком дорого для запуска, так как он вычисляет полное разделение каждый раз, когда вызывающая сторона запрашивает один из элементов...
Решение, представленное ниже Стюартом Эштоном по этой ссылке, очень удобно. Он устраняет необходимость в том, чтобы список значений был целочисленным, поэтому вы можете использовать список строк.
В предложении WITH он заключает значения в одинарные кавычки, а затем преобразует их в таблицу с одним столбцом типа VARCHAR2. https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/
with data as (
select '"'||replace(:txt, ',', '","')||'"' str from dual
)
select xmlcast(column_value as varchar2(4000)) subs
from data, xmltable(str);
declare
seprator varchar2(1):=',';
dosweeklist varchar2(4000):='a,b,c';
begin
for i in (SELECT SUBSTR(dosweeklist,
case when level=1 then 1 else INSTR(dosweeklist,seprator,1,LEVEL-1)+1 end,
NVL(NULLIF(INSTR(dosweeklist,seprator,1,LEVEL),0),length(dosweeklist)+1) - case when level=1 then 1 else INSTR(dosweeklist,seprator,1,LEVEL-1)+1 end) dat
FROM dual
CONNECT BY LEVEL <= LENGTH(dosweeklist) - LENGTH(REPLACE(dosweeklist,seprator,'')) +1)
loop
dbms_output.put_line(i.dat);
end loop;
end;
/
поэтому выберите запрос только в цикле for, заменив doweeklist в качестве строки с разделителями и разделитель в качестве символа с разделителями.
Давайте посмотрим вывод
a
b
c
Вы можете использовать функцию замены, чтобы легко заменить запятую. Сделать это-
Синтаксис функции REPLACE в SQL Server (Transact-SQL):
REPLACE(строка, строка_для замены, строка_замены)
Параметры или аргументы
string : Исходная строка, из которой последовательность символов будет заменена другим набором символов.
string_to_replace : Строка, которая будет искаться в строке 1.< br> replacement_string : строка замены. Все вхождения string_to_replace будут заменены на replace_string в строке1.
Примечание.
Функция REPLACE выполняет замену без учета регистра. Таким образом, все вхождения string_to_replace будут заменены на replace_string независимо от случая string_to_replace или replace_string.
Например:
SELECT REPLACE('Kapil,raj,chouhan', ',' , ' ') from DUAL;
Результат: Kapil raj chouhan
ВЫБЕРИТЕ ЗАМЕНУ('Я живу в Индии', ' ' , '-') из DUAL;
Результат: Я живу в Индии
ВЫБЕРИТЕ ЗАМЕНУ('facebook.com', 'face' , 'friends') из DUAL;
Результат: friendsbook.com
Я надеюсь, что это будет полезно для вас.