Из неопубликованного. Забытые regexp этюды для работы со строковым представлением дробных чисел со степенью вида [+/-]NNN[.MMM[E[+/-]PPP]]

    Точно не помню, зачем это мне было нужно, но теперь, по прошествии нескольких лет, выглядит просто интересно. Насколько я помню, была у меня попытка написать регулярное выражение, которое могло бы обработать строку и определить, корректно ли написано число, чтобы не возникала исключительная ситуация Дополнительно к этому для сравнения написал функцию isNumber(), которая проверяла бы корректность написания числа с помощью вызова to_number() с гашением исключительных ситуаций.

with  

    function isNumber(p_String in varchar2) return number is 

      n number; 

    begin 

      n := to_number(p_String); 

      return 1; 

    exception when others then return 0; 

    end; 

 

select * from ( 

    with 

        q_pattern as ( 

            select '^'|| 

            '([-+]?)'|| 

            '('|| 

                '([[:digit:]]+[.,]?)(E[-+]?[[:digit:]]+)?|'|| -- целое число с научной нотацией 

                '([[:digit:]]*([.,][[:digit:]]+))(E[-+]?[[:digit:]]+)?'|| -- дробное число с научной нотацией 

            ')$' pattern from dual 

        ), 

        q_nums as (select '.1,1.,.,1.E1,E2,1,-1,.1E1,.1E-1,-.1E+1,1.,-1.,.E3,.0E3, -.0E3, 1.23847,-1.23847,22E2, 22E-2, 22F2, 22E2.2, 1L,0x12, .1112E-1,-.1E-10, 123.2344E1, 123.2344E11.1' num_list from dual), 

        q_tab as (select regexp_substr(num_list, '\s*([^,]+)\s*',1,level,'i',1) num from q_nums connect by regexp_substr(num_list, '[^,]+',1,level) is not null) 

    select 

        num, 

        case when regexp_like(num, pattern) then 'Ok' else 'Fail' end by_regexp_like, 

        case when isNumber(num) = 1 then 'Ok' else 'Fail' end by_to_number 

    from 

        q_tab 

        cross join q_pattern 

) t

Токенизация строки с помощью xmltable

    Посмотрел я свой бложик ретроспективно... Блин, столько лет работы, а в нем про одно разбиение по разделителю да работа с CSV. Уныло как-то стало. А потом задумался, - но ведь это же довольно значимая часть работы любого разработчика баз данных. Мы проводим часы и дни в поисках оптимальных решений для обработки данных, выискивая наилучшие подходы и поэтому выбор инструментов у нас по любой проблеме (даже такой, казалось бы, примитивной, как токенизация) должен быть достаточно широк. 
    Поэтому решил добавить еще один удобный вариант разбиения на токены, который используется у нас при работе с фронтендом. При данном подходе, разумеется, ограничена длина списка токенизируемых значений, поэтому применять нужно с оглядкой на возможную максимальную длину входной строки. 

 

select 

    xt.item 

from 

    xmltable(

        'for $text in tokenize($input, ",") return $text' 

        passing 'Северный Кавказ,Волга-Север,Северо-Запад' as "input" 

        columns item varchar2(4000) path '.'

    ) xt

Вывод:

ITEM

----------- 

Северный Кавказ

Волга-Север

Северо-Запад

PL/SQL парсер для CSV данных, находящихся в CLOB-переменной (в соотв. с RFC 4180)

Ну, вот и снова пришлось написать что-то набившее оскомину. На этот раз - очередной парсер для CLOB-переменной, содержащей CSV данные, сохраненные в формате Excel. Порылся в stackoverflow, ничего толкового не нашел, поэтому понял, что быстрее написать самому. Парсит достаточно большие файлы. У меня есть образец на 9Мб который достаточно быстро обрабатывается, разумеется без вставки в БД, просто с выводом в output. Потом, подумал, что надо бы сделать пример и потом самому к нему обращаться при необходимости, чтобы не рыться в каталогах. 

Итак, 

- входное значение указано в CLOB-переменной c. 

- в качестве разделителя значений используется ";"

- символ экранирования многострочных значений - двойная кавычка, 

- символ двойной кавычки - двойная двойная кавычка

- cимвол новой строки - 0x0A (10)

- результат выводится в output буфер

declare
    -- CLOB value for tests, separator is semicolon, has been copied from file saved as Windows-1251 CSV worksheet by Excel 2019
    c clob := '"1";"Serge;
George
Galina";3
""2"";Tatiana;3
3;"Nikolas
""Maxim""
Lisa";3
4;;3
5;"Daria;";3
6;""Leo"";3
7;Alexey;""3""
8;Ksenia;"3"';
    -- special characters
    separator       constant char(1) := ';';   -- semicolon separator
    quote_char      constant char(1) := '"';  -- quoting character
    new_row         constant char(1) := chr(10); -- new row character
    -- output cell value as a comment
    procedure cell_out(prow in number, pcol in number, pval in varchar2) is
    begin
        dbms_output.put_line('Cell ('||prow||', '||pcol || ') = '|| nvl(replace(pval,new_row,'<\n>'), '<null>'));
    end;
    -- CSV parser
    procedure parse_csv_clob (p_clob in out nocopy clob) is
        offset          number := 1;
        amount          number := 1;
        total           number := nvl(dbms_lob.getlength(p_clob),0);
        read_buffer     varchar2(1);
        read_buffer1    varchar2(1);
        cell_buffer     varchar2(32767);
        row_index       pls_integer := 1;
        col_index       pls_integer := 1;
        quoting         boolean := false;
    begin
        if ( dbms_lob.isopen(p_clob) != 1 ) then dbms_lob.open(p_clob, 0); end if;
        loop
            exit when ( offset > total );
            dbms_lob.read(p_clob, amount, offset, read_buffer); -- read the symbol
            if read_buffer = new_row and not quoting then -- new row
                cell_out(row_index, col_index, cell_buffer);
                row_index := row_index + 1;
                col_index := 1;
                cell_buffer := null;
            elsif read_buffer = separator and not quoting then -- new cell
                cell_out(row_index, col_index, cell_buffer);
                cell_buffer := null;
                col_index := col_index + 1;
            elsif read_buffer = quote_char then -- quoted value or a escaped double quote symbol (if next symbol is the same)
                if offset < total then dbms_lob.read(p_clob, amount, offset + 1, read_buffer1); else read_buffer1 := null; end if;
                if read_buffer1 is null or read_buffer1 != quote_char then quoting := not quoting;
                else 
                    cell_buffer := cell_buffer || quote_char;
                    offset := offset + 1;
                end if;
            else -- add any other symbols to a current cell value
                cell_buffer := cell_buffer || read_buffer;
            end if;
            offset := offset + 1;
            if (offset > total and not quoting) then
                if length(cell_buffer) > 0 then
                    cell_out(row_index, col_index, cell_buffer);
                end if;
            end if;
        end loop; 
        if ( dbms_lob.isopen(p_clob) = 1 ) then dbms_lob.close(p_clob); end if; 
        if quoting then raise_application_error(-20001, 'Closing quote character has not been found.'); end if;
    exception
        when others then
            dbms_output.put_line('CSV-PARSER-ERROR: '||sqlerrm);
    end;
begin
    parse_csv_clob(c);
end;