Oracle преобразовать строку в массив
У меня есть вход ' 1,2,3' , и мне нужно преобразовать его в массив.
вот еще один более простой вариант
Oracle предоставляет встроенную функцию DBMS_UTILITY. COMMA_TO_TABLE .
К сожалению, этот не работает с номерами:
Но с небольшим трюком для префикса элементов с 'x', это работает:
С уважением, Роб.
У нас никогда не останется альтернативы делать одно и то же по-разному, верно? Я недавно нашел это очень удобно:
Я знаю, что переполнение стека хмурится при вставке URL-адресов без объяснений, но у этой конкретной страницы есть несколько действительно хороших вариантов:
Мне особенно нравится этот, который преобразует список с разделителями во временную таблицу, к которой вы можете выполнять запросы:
Да, это очень расстраивает, что dbms_utility. comma_to_table поддерживает только списки, разделенные запятыми, и только тогда, когда элементы в списке являются допустимыми идентификаторами PL / SQL (поэтому числа вызывают ошибку).
Посмотрим на выход :
Это не экспертный ответ, поэтому я надеюсь, что кто-то улучшит этот ответ.
Быстрый поиск на моем BBDD привел меня к функции под названием split:
Я не знаю, будет ли это полезно, но мы нашли это здесь . , ,
Запускать его слишком дорого, так как он вычисляет полное разбиение каждый раз, когда вызывающий абонент запрашивает один из элементов в нем. , ,
Вы можете использовать функцию замены, чтобы легко заменить запятую. Делать это -
Синтаксис для функции REPLACE в SQL Server (Transact-SQL):
ЗАМЕНИТЬ (строка, строка_место, замена_строка)
Параметры или аргументы
string: Исходная строка, из которой последовательность символов будет заменена другим набором символов.
string_to_replace: Строка, которая будет искать в string1.
replace_string: Строка замены. Все вхождения string_to_replace будут заменены замещающей строкой в string1.
Примечание :
Функция REPLACE выполняет замену, которая не с учетом регистра. Таким образом, все вхождения string_to_replace будут заменено на replace_string независимо от случая string_to_replace или replace_string
Например:
ВЫБЕРИТЕ ЗАМЕНУ ( 'Kapil, raj, chouhan', ',', '' ) от DUAL;
Результат: Капил радж чухан
ИЗБРАННОЕ ЗАМЕНА ( «Я живу в Индии», «,» - «) из ДВОЙНОГО;
Результат: я живу в Индии
ВЫБРАТЬ ЗАМЕНУ ( 'facebook. com ',' face ',' friends ') от DUAL;
Результат: книга друзей. com
Edit: Oracle: 9i
The "table" reference tends to be a hangover from the old PL/SQL tables naming. VARRAYs, Associative Arrays and Declared nested tables are all in-memory array types.
15 Answers 15
here is another easier option
Wow! That just reads so nicely for PL/SQL: FOR i IN (SELECT to_number(column_value) as ID FROM xmltable('1,2,3,4,5')) LOOP. END LOOP; is just most excellent, thank you!
Alas, this gives ORA-01460: unimplemented or unreasonable conversion requested for a line >4,000 bytes long
Unfortunately, this one doesn't work with numbers:
But with a little trick to prefix the elements with an 'x', it works:
Good catch. It also doesn't work with special characters. You could work around this limitation by doing some extra special "replaces". For example, use replace(. ' ','XYZ') when entering the function and replace(. 'XYZ',' ') when retrieving the individual values.
From: Converting delimited lists to collections (and vice versa) COMMA_TO_TABLE (and the reverse TABLE_TO_COMMA ) are not written for this purpose! . They are written primarily for use within replication internally by Oracle, and parse IDENTIFIERS rather than strings, and as such have to be valid Oracle object names.
Error when used a string with "." Ex: '8.5.17.1,8.5.17.2' Error is ORA-20001: comma-separated list invalid near x8.5. Can you help resolve this.
The functions used here are merely useful to split a list of table-names or similar valid object names/identifiers. See the Oracle documentation. Do not follow this solution, as it will result in undefined behaviour for "random"/"arbitrary" string-lists.
We can never run out of alternatives of doing the same thing differently, right? I recently found this is pretty handy:
This works efficiently only for small lists as it produces a cross-join before selecting rows where the corresponding match count ( LEVEL ) is the same. If your lists are likely to grow over time this presents a scalability risk.
I know Stack Overflow frowns on pasting URLs without explanations, but this particular page has a few really good options:
I particularly like this one, which converts the delimited list into a temporary table you can run queries against:
This solution is the only one which works if you have a string with spaces and consecutive commas (i.e. 12 3,456,,abc,def). Searched for 4 hours, till I fount this.
This function doesn't answer the question. The post asks for an array of all elements in the list. This function provides access to a single element in the array by index.
Let's see the output:
A quick search on my BBDD took me to a function called split:
I don't know if it'll be of use, but we found it here.
Uops! I did not see your answer! It works quite well actually, I have it stored in my Usefull Functions library ;)
This is not an expert answer so I hope someone would improve this answer.
Another possibility is:
It's a little too expensive to run, as it computes the complete split every time the caller asks for one of the items in there.
The solution presented below by Stewart Ashton in this link is pretty handy. He eliminates the need for the value list to be integer, so you can use string list.
so select query only in for loop can do the trick, by replacing dosweeklist as your delimited string and seprator as your delimited character.
Lets see output
You can use Replace Function to replace comma easily. To Do this-
The syntax for the REPLACE function in SQL Server (Transact-SQL) is:
REPLACE( string, string_to_replace, replacement_string )
Parameters or Arguments
string : The source string from which a sequence of characters will be replaced by another set of characters.
string_to_replace : The string that will be searched for in string1.
replacement_string : The replacement string. All occurrences of string_to_replace will be replaced with replacement_string in string1.
Note :
The REPLACE function performs a replacement that is not case-sensitive. So all occurrences of string_to_replace will be replaced with replacement_string regardless of the case of string_to_replace or replacement_string
For Example :
SELECT REPLACE('Kapil,raj,chouhan', ',' , ' ') from DUAL;
Result : Kapil raj chouhan
SELECT REPLACE('I Live In India', ' ' , '-') from DUAL;
Result : I-Live-In-India
Я работаю PL/SQL разработчиком. Есть задача собирать некоторые данные для метрик, чтобы отслеживать загрузку систем. Есть некоторая функция, которая вызывается с параметром, состоящим из списка ID.
Задача заключается в следующем. Нужно разбить такую строку на элементы и записать их в целочисленную коллекцию.
Приступим.
Для начала нужны данные для работы. Напишем функцию, которая генерирует строку с числами, разделенными запятой. В функцию будем передавать целочисленный аргумент N – количество чисел в последовательности.
Мудрить не будем, последовательность сделаем с типом VARCHAR2, а не CLOB. Далее объясню, почему именно VARCHAR2.
Код функции для генерации последовательности:
Вернёмся к нашей задаче.
Первое, что приходит на ум, это сделать цикл по строке, с уменьшением длинны строки на каждой итерации. Так как по условию задачи результат нужно поместить в коллекцию, то создадим соответствующую коллекцию.
Результат:
0
1
2
…
421
422
423
…
Функция createNumber() принимает аргумент v_N = 1000. В функции createNumber() можно видеть обработку переполнения переменной v_str. Нехитрым подсчётом можно выяснить, что 4000 байт хватит для 1021 чисел. Наша 1000 без проблем влезает в этот размер.
Как видно, результат тот, который нужен был. Строка разделена.
Пусть даже в Oracle нет встроенной функции split(), как например в Java или Python, но данный вариант меня не устраивает, так как я считаю, что слишком много кода написано для такой простой задачи как разбиение строки.
На данном этапе я задумался, а можно ли разбить строку только средствами SQL? Я имею ввиду не классический SQL, а тот SQL, который предлагает Oracle.
Я вспомнил про конструкцию для построения иерархических запросов CONNECT BY.
Необязательный оператор START WITH говорит Oracle с чего начинать цикл, т.е. какая строка будет корневой. Условие может быть практически любым. Условие после CONNECT BY нужно указать обязательно. Тут надо сказать Oracle, как долго продолжать цикл.
Видно, что единственно важное условие для построения иерархического запроса – это оператор CONNECT BY, остальное «нанизывается» по мере надобности.
Также у этой конструкции есть псевдостолбец level, который возвращает уровень вложенности на текущей итерации.
На первый взгляд может показаться, что данная конструкция для разбиения строки не подходит. Это не совсем так. Если правильно задать условие, то рекурсивный обход можно превратить в циклический, как в циклах while или for.
Прежде чем писать запрос, обдумаем алгоритм обхода строки. Нужно, начиная от начала строки, отрезать некоторое количество символов, до символа разделителя. Выше я писал про псевдостолбец level. Мы его будем использовать, как номер текущей итерации.
Получается что-то такое:
Но если присмотреться, то можно увидеть, что данный алгоритм не сработает на самой первой итерации, так как третий аргумент функции INSTR() не может равняться 0.
Поэтому добавим небольшое условие с помощью функции DECODE().
Теперь самая первая итерации будет отрабатывать корректно.
Пора бы применить конструкцию CONNECT BY. Плюс вынесем нашу строку наверх.
Я уже писал, что при правильном условии конструкция CONNECT BY сможет вести себя подобно циклу. Условие выполняется до тех пор, пока функция INSTR() может найти n-ую позицию символа разделителя, где n – это номер текущей итерации, а как мы помним за номер итерации отвечает псевдостолбец level.
Вроде бы задача решена? Нет.
Код может и работает, но его читаемость нулевая. Я уже думал вернуться к варианту с циклом, но придумал как улучшить вариант с CONNECT BY.
В Oracle есть такое мощное средство, как регулярные выражения. Конкретно функции regexp_instr() и regexp_substr().
regexp_instr(исходная_строка, шаблон[, начальная_позиция [, вхождение ] ]) — функция возвращает позицию символа, находящегося в начале или конце соответствия для шаблона, так же как и ее аналог INSTR().
regexp_substr(исходная_строка, шаблон[, позиция [, вхождение ]]) — функция возвращает подстроку, которая соответствует шаблону.
Перепишем запрос, используя регулярные выражения:
Код читается намного лучше, чем в предыдущем примере. Такой вариант меня устраивает.
В конце было бы логичным привести сравнения времени выполнения разбора строки для трёх вариантов. Выше я обещал объяснить, почему вместо типа CLOB будем использовать тип VARCHAR2. Это нужно как раз для сравнения времени выполнения. Так как Oracle обрабатывает тип CLOB по-другому, чем VARCHAR2, что может исказить результаты.
Сперва мы узнаем, кто был в мужьях у этих красоток. А потом с помощью незамысловатых спецэффектов я вам покажу, в каком порядке они друг с другом бракосочетались. Так что юным девам эта статья будет особенно интересна.
Создадим и заполним базовую таблицу
ID | Актриса | Мужья |
---|---|---|
1 | Анджелина Джоли | Джонни Ли Миллер, Билли Боб Торнтон, Брэд Питт |
2 | Шарлиз Терон | |
3 | Пенелопа Крус | Хавьер Бардем |
Из таблицы видно, что Анжелика была замужем трижды. Ее мужья перечислены в колонке через разделитель в порядке очередности их бракосочетания с актрисой. Условимся, что разделитель — это запятая, а пробел после нее — просто мусор.
Лиза Терон вообще ни разу не была замужем (гражданские браки не в счет), и она, по всей видимости, до сих пор ждет своего айтишника. Так что следует взять это на заметку и как следует поторопиться — даме уже, без малого, 40.
Ну и Пенелопа Крус — замужем всего один раз. Какая скука.
Но это все прелюдия, а на деле нужно получить следующий результат
Актриса | Муж | Номер мужа п/п |
---|---|---|
Анджелина Джоли | Джонни Ли Миллер | 1 |
Анджелина Джоли | Билли Боб Торнтон | 2 |
Анджелина Джоли | Брэд Питт | 3 |
Шарлиз Терон | ||
Пенелопа Крус | Хавьер Бардем | 1 |
По сути надо выполнить операцию, обратную группировке и агрегации функцией listagg.
Будем двигаться от простого к сложному. Для начала предлагаю рассмотреть похожую задачу — извлечение чисел из одиночной строки с разделителем в табличный набор.
- Генерируются новые строки с помощью connect by level.
- Через regexp_count вычисляется количество чисел в строке между разделителями – это количество определяет верхнюю границу для генератора строк.
- С помощью regexp_substr извлекаются числа из строки. Номер вхождения шаблона в строку (4-й аргумент регулярки) соответствует значению псевдостолбца rownum — номер п/п сгенерированной строки. Вместо rownum можно было использовать и level, результат был бы аналогичным.
В таблице HOLLYWOOD мы имеем дело не с последовательностью чисел, а с именами знатных мужей. Но их можно также подсчитать с помощью функции regexp_count и извлекать, используя функцию regexp_substr, согласно вышеописанной методе. Теперь нужно вспомнить рецепты маринования бананов и выбрать один из способов генерации строк, когда известно их будущее количество. Для примеров я воспользуюсь 3-м и 5-м способом. Но при выборе наиболее оптимального метода генерации нужно обратить внимание на коммент пользователя xtender.
Объединив подходы, получаем следующее.
Спецэффект № 1.
Всё ОК – девушки счастливы в браках. Все, кроме Лизы Терон. Для таких, как Лиза, в запросе я использовал nvl2.
Спецэффект № 2.
Это было решение через коллекции.
UPD: Нарисовался еще один замечательный Спецэффект № 3 для Oracle 12c
Очевидно, что вариантов решения задачи существует немало. Выбор того или иного варианта – дело вкуса и вопрос производительности. Если бы у меня был Оскар, я бы его не задумываясь вручил тому, кто предложит наиболее лаконичный с точки зрения кода и наиболее оптимальный с точки зрения производительности способ.
6 Answers 6
You can use VARRAY for a fixed-size array:
Or TABLE for an unbounded array:
The word "table" here has nothing to do with database tables, confusingly. Both methods create in-memory arrays.
With either of these you need to both initialise and extend the collection before adding elements:
The first index is 1 not 0.
@Abdul, no it doesn't. I never use VARRAYs normally but when testing the above code I checked what happens if you try to extend a varray(3) 4 times - you get a "subscript out of limit" error.
Wish I coud up vote this answer multiple times @TonyAndrews since you covered the array.extend() . Every where I looked did not show this and it was the most important part to being able to add more than one item (from my understanding of it, still new to arrays in SQL).
You could just declare a DBMS_SQL.VARCHAR2_TABLE to hold an in-memory variable length array indexed by a BINARY_INTEGER:
You could use an associative array (used to be called PL/SQL tables) as they are an in-memory array.
The associative array can hold any make up of record types.
Hope it helps, Ollie.
The iteration condition raises VALUE_ERROR when the collection is empty. I would suggest to rather use FOR i IN 1 .. employee_array.COUNT in this case
You can also use an oracle defined collection
I would use in-memory array. But with the .COUNT improvement suggested by uziberia:
Another solution would be to use a Hashmap like @Jchomel did here.
NB:
Another solution is to use an Oracle Collection as a Hashmap:
plsql table or associated array.
Declare your public array type to be use in script
This is the function you need to call - simply finds the values in the string passed in using a comma delimiter
Finally Declare a local variable, call the function and loop through what is returned
How do I convert a comma separated string to a array?
I have the input ' 1,2,3' , and I need to convert it into an array.
Читайте также: