GoldenLine
Zaloguj się
Izabela Korzińska

Izabela Korzińska Architekt /
Developer ETL/TEam
Leader, Roche Polska

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Czy wiecie coś o istnieniu w Oracle'u funkcji, która z ciągu:

a,b,c,d

zrobiłaby 4 rekordy:

a
b
c
d

Kolega potrzebuje czegoś takiego. W językach programowania taka funkcja to np. SPLIT.
Link do wypowiedziLink

konto usunięte

Temat: ORACLE - odwrotność funkcji WM_CONCAT

http://it.toolbox.com/wiki/index.php/Splitting_string_...
Link do wypowiedziLink
Łukasz Schabek

Łukasz Schabek Architekt Rozwiązań

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Funkcji nie ma, najlepiej napisać sobie procedurę. Jeśli jednak chcesz pokombinować, spróbuj tak:



SELECT REPLACE(SUBSTR(FOO, SEQ.LP, INSTR(FOO, ',')), ',','') AS BAR

FROM (

    SELECT 'a,b,c,d' AS FOO

    FROM DUAL 

) L0

FULL JOIN (

    SELECT 1 AS LP FROM DUAL UNION ALL

    SELECT 3 AS LP FROM DUAL UNION ALL

    SELECT 5 AS LP FROM DUAL UNION ALL

    SELECT 7 AS LP FROM DUAL

) SEQ -- ciąg arytmetyczny

ON 1 = 1
Łukasz Schabek edytował(a) ten post dnia 25.03.11 o godzinie 12:04
Link do wypowiedziLink
Monika Y.

Monika Y. IT

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Ja też nie słyszałam o takiej funkcji... Znalazłam kiedyś fajne rozwiązanie ale to dla bazy od wersji 10:



WITH tab AS (SELECT 'a,b,c,d' kol

             FROM dual

          )

SELECT REGEXP_SUBSTR(kol, '[^,]+', 1, ROWNUM) kol_2

  FROM tab

  CONNECT BY level <= LENGTH(REGEXP_REPLACE(kol_2, '([^,])+|.', '\1'))
Link do wypowiedziLink

konto usunięte

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Schabek:
Funkcji nie ma, najlepiej napisać sobie procedurę. Jeśli jednak chcesz pokombinować, spróbuj tak:



SELECT REPLACE(SUBSTR(FOO, SEQ.LP, INSTR(FOO, ',')), ',','') AS BAR

FROM (

    SELECT 'a,b,c,d' AS FOO

    FROM DUAL 

) L0

FULL JOIN (

    SELECT 1 AS LP FROM DUAL UNION ALL

    SELECT 3 AS LP FROM DUAL UNION ALL

    SELECT 5 AS LP FROM DUAL UNION ALL

    SELECT 7 AS LP FROM DUAL

) SEQ -- ciąg arytmetyczny

ON 1 = 1

To czemu nie od razu:

select 'a' from dual union all
select 'b' from dual union all
select 'c' from dual union all
select 'd' from dual

?Ireneusz Ptak edytował(a) ten post dnia 25.03.11 o godzinie 15:18
Link do wypowiedziLink
Irek Słonina

Irek Słonina programowanie, bazy
danych i linuksy

Temat: ORACLE - odwrotność funkcji WM_CONCAT



create or replace

TYPE SPLIT_TBL as table of varchar2(32767);

/



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;



    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;

/



select * from table(split('a,b,c,d')) ;

COLUMN_VAlue

--------

a

b

c

d
Irek Słonina edytował(a) ten post dnia 25.03.11 o godzinie 17:02
Link do wypowiedziLink
Łukasz Schabek

Łukasz Schabek Architekt Rozwiązań

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Ireneusz Ptak:

To czemu nie od razu:

select 'a' from dual union all
select 'b' from dual union all
select 'c' from dual union all
select 'd' from dual

?

Nie, no jak? Skąd będziesz wiedział jakie wartości znajdą się w kolumnie FOO?
Link do wypowiedziLink
Łukasz Kurowski

Łukasz Kurowski Usque Ad Finem

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Schabek:
Ireneusz Ptak:

To czemu nie od razu:

select 'a' from dual union all
select 'b' from dual union all
select 'c' from dual union all
select 'd' from dual

?

Nie, no jak? Skąd będziesz wiedział jakie wartości znajdą się w kolumnie FOO?

Wydaje mi się, że koledze chodziło raczej o to, że równie dobrze nie ma pewności, że będą cztery jednoznakowe wartości...
Link do wypowiedziLink
Łukasz Schabek

Łukasz Schabek Architekt Rozwiązań

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Kurowski:

Wydaje mi się, że koledze chodziło raczej o to, że równie dobrze nie ma pewności, że będą cztery jednoznakowe wartości...

Oczywiście, nie ma pewności. Nie podałem gotowego rozwiązania, a jedynie ideę jak można podejść do tematu.

W podanym przykładzie, kiedy w kolumnie znajdą się pojedyncze znaki rozdzielone przecinkiem, ich występowanie można opisać prostym ciągiem arytmetycznym 1,3,5...n (ciąg kolejnych liczb nieparzystych).
Bez względu więc na to, czy znaków będzie cztery czy tysiąc, rozwiązanie takie zadziała bez konieczności pisania funkcji.

Inny przykład to rozwijanie zakresów numeracyjnych:



SELECT

	nr_a+b.day_of_calendar

FROM (

	SELECT 10000 AS nr_a, 11100 AS nr_b

) a

JOIN	(

	SELECT day_of_calendar

	FROM SYS_CALENDAR.CALENDAR

) b

ON	b.day_of_calendar < nr_b-nr_a


Tutaj jako sekwencja użyta została tabela systemowa w bazie Teradata. W rezultacie otrzymamy 1099 rekordów z kolejnymi numerami. W przypadku bezy TD działa to nieporównanie szybciej niż zdefiniowana przez użytkownika funkcja czy makro.

Reasumując, jeśli jakieś przekształcenie lub format da się opisać prostym ciągiem arytmetycznym, można użyć ww. metody.
Link do wypowiedziLink
Łukasz Kurowski

Łukasz Kurowski Usque Ad Finem

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Schabek:
Łukasz Kurowski:

Wydaje mi się, że koledze chodziło raczej o to, że równie dobrze nie ma pewności, że będą cztery jednoznakowe wartości...

Oczywiście, nie ma pewności. Nie podałem gotowego rozwiązania, a jedynie ideę jak można podejść do tematu.

W podanym przykładzie, kiedy w kolumnie znajdą się pojedyncze znaki rozdzielone przecinkiem, ich występowanie można opisać prostym ciągiem arytmetycznym 1,3,5...n (ciąg kolejnych liczb nieparzystych).
Bez względu więc na to, czy znaków będzie cztery czy tysiąc, rozwiązanie takie zadziała bez konieczności pisania funkcji.

Przypuśćmy, że wartości może być od jednej do maksymalnie tysiąca. Kartezjan z tysiącem rekordów, bo tyle musi być, żeby obsłużyć najgorszy przypadek to chyba nigdzie nie jest dobry pomysł. No jakby rekordów do przerobienia było niewiele, to jeszcze jako tako, ale co w przypadku tabeli z milionami rekordów? Ze złączenia wyjdą miliardy wierszy... Chyba już lepiej to oprogramować :)
Link do wypowiedziLink
Izabela Korzińska

Izabela Korzińska Architekt /
Developer ETL/TEam
Leader, Roche Polska

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Irek Słonina:


create or replace

TYPE SPLIT_TBL as table of varchar2(32767);

/



create or replace

function split

(

    p_list varchar2,

    p_del varchar2 := ','

) return split_tbl pipelined

is

[...]

/



select * from table(split('a,b,c,d')) ;

COLUMN_VAlue

--------

a

b

c

d

To moje ulubione rozwiązanie, tylko działa pięknie w SQLPlusie, a w Toadzie niestety nie wpisuje mi niczego do tych zwracanych 4 rekordów i nie bardzo kumam, dlaczego...
Dzięki Irku :)

Co do innych rozwiązań:
1. Liczba fragmentów txt, oddzielonych przecinkiem jest różna.
2. Kumpla interesują tylko takie rozwiązania, które podzielą tekst na części i wyświetlą każdą z tych części w osobnym rekordzie.
3. Teksty, oddzielone przecinkami mają różną długość.
Link do wypowiedziLink
Izabela Korzińska

Izabela Korzińska Architekt /
Developer ETL/TEam
Leader, Roche Polska

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Michał Z.:
http://it.toolbox.com/wiki/index.php/Splitting_string_...

No i jeszcze - dzięki Michał :)
takie rozwiązanie też działa:


SELECT *

  FROM (SELECT trim ( substr (

                               txt

                              ,instr (

                                       txt, ',', 1

                                      ,level

                                      )

                               + 1

                              ,  instr (

                                         txt

                                        ,','

                                        ,1

                                        ,level

                                         + 1

                                        )

                               - instr (

                                         txt, ',', 1

                                        ,level

                                        )

                               - 1

                              ) )

                  AS token

          FROM (SELECT    ','

                       || :in_string

                       || ','

                          AS txt

                  FROM dual)

        CONNECT BY level <=   length ( txt )

                            - length ( replace (

                                                 txt, ',', ''

                                                ) )

                            - 1)
Link do wypowiedziLink
Łukasz Schabek

Łukasz Schabek Architekt Rozwiązań

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Kurowski:
>
Przypuśćmy, że wartości może być od jednej do maksymalnie tysiąca. Kartezjan z tysiącem rekordów, bo tyle musi być, żeby obsłużyć najgorszy przypadek to chyba nigdzie nie jest dobry pomysł. No jakby rekordów do przerobienia było niewiele, to jeszcze jako tako, ale co w przypadku tabeli z milionami rekordów? Ze złączenia wyjdą miliardy wierszy... Chyba już lepiej to oprogramować :)

Jakkolwiek zrobisz, dostaniesz milady wierszy. I jak słusznie zauważyłeś, przy dużej ilości rekordów może być problem z wydajnością, również w działaniu funkcji. Czasem funkcja czy procedura, przez inicjowanie zmiennych, kursorów, tablic itp, może zajmować więcej zasobów niż kwerenda. Warto to wziąć pod uwagę.
Link do wypowiedziLink
Łukasz Kurowski

Łukasz Kurowski Usque Ad Finem

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Schabek:
Jakkolwiek zrobisz, dostaniesz milady wierszy.

Właśnie nie...

Przypuśćmy, że masz 2 mln rekordów "a,b,c" i jeden rekord "a,b......."(kropki to reszta tysięcy znaków). Kartezjan zawsze da miliardy rekordów w kroku pośrednim, a procedura da 6 mln plus te kilka tysięcy.
Link do wypowiedziLink
Łukasz Schabek

Łukasz Schabek Architekt Rozwiązań

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Kurowski:
Łukasz Schabek:
Jakkolwiek zrobisz, dostaniesz milady wierszy.

Właśnie nie...

Przypuśćmy, że masz 2 mln rekordów "a,b,c" i jeden rekord "a,b......."(kropki to reszta tysięcy znaków). Kartezjan zawsze da miliardy rekordów w kroku pośrednim, a procedura da 6 mln plus te kilka tysięcy.

Nie kumam. Skąd te 2 mln rekordów? Przecież dane są przechowywane jako oddzielone przecinkiem symbole w jednym polu.
Link do wypowiedziLink
Łukasz Kurowski

Łukasz Kurowski Usque Ad Finem

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Schabek:
Łukasz Kurowski:
Łukasz Schabek:
Jakkolwiek zrobisz, dostaniesz milady wierszy.

Właśnie nie...

Przypuśćmy, że masz 2 mln rekordów "a,b,c" i jeden rekord "a,b......."(kropki to reszta tysięcy znaków). Kartezjan zawsze da miliardy rekordów w kroku pośrednim, a procedura da 6 mln plus te kilka tysięcy.

Nie kumam. Skąd te 2 mln rekordów? Przecież dane są przechowywane jako oddzielone przecinkiem symbole w jednym polu.

No z tego co rozumiem, to nie jest to jeden rekord z wartościami przedzielonymi przecinkiem... Może być ich wiele np.
a,b
a,b,c
a,b,c,d
i tak dalej.
Link do wypowiedziLink
Łukasz Schabek

Łukasz Schabek Architekt Rozwiązań

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Kurowski:

No z tego co rozumiem, to nie jest to jeden rekord z wartościami przedzielonymi przecinkiem... Może być ich wiele np.
a,b
a,b,c
a,b,c,d
i tak dalej.

Też tak zrozumiałem, bez różnicy więc czy użyjesz funkcji split czy zapytania z wyrażeniem regularnym czy z klauzulą FULL JOIN którą przytoczyłem, otrzymasz tyle samo wyników.
Link do wypowiedziLink
Łukasz Kurowski

Łukasz Kurowski Usque Ad Finem

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Schabek:
Łukasz Kurowski:

No z tego co rozumiem, to nie jest to jeden rekord z wartościami przedzielonymi przecinkiem... Może być ich wiele np.
a,b
a,b,c
a,b,c,d
i tak dalej.

Też tak zrozumiałem, bez różnicy więc czy użyjesz funkcji split czy zapytania z wyrażeniem regularnym czy z klauzulą FULL JOIN którą przytoczyłem, otrzymasz tyle samo wyników.

5 rekordów na wejściu w każdym po 5 wartości. Split wyrzuci 5*5 wierszy, a FULL JOIN na warunku 1=1 to przecież kartezjan, który rozmnoży każdy rekord przez tyle na ile maksymalnie wartości był obliczony.
Link do wypowiedziLink
Łukasz Schabek

Łukasz Schabek Architekt Rozwiązań

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Kurowski:

5 rekordów na wejściu w każdym po 5 wartości. Split wyrzuci 5*5 wierszy, a FULL JOIN na warunku 1=1 to przecież kartezjan, który rozmnoży każdy rekord przez tyle na ile maksymalnie wartości był obliczony.

Ograniczasz więc wartości. Zobacz przykład z rozwijaniem zakresów numeracji, tam zamiast 1=1 łączysz tak:

ON b.day_of_calendar < nr_b-nr_a

Tu już nie ma kartezjana, prawda? W przypadku pierwszym robisz tak samo.
Link do wypowiedziLink
Łukasz Kurowski

Łukasz Kurowski Usque Ad Finem

Temat: ORACLE - odwrotność funkcji WM_CONCAT

Łukasz Schabek:
Łukasz Kurowski:

5 rekordów na wejściu w każdym po 5 wartości. Split wyrzuci 5*5 wierszy, a FULL JOIN na warunku 1=1 to przecież kartezjan, który rozmnoży każdy rekord przez tyle na ile maksymalnie wartości był obliczony.

Ograniczasz więc wartości. Zobacz przykład z rozwijaniem zakresów numeracji, tam zamiast 1=1 łączysz tak:

ON b.day_of_calendar < nr_b-nr_a
>
Tu już nie ma kartezjana, prawda? W przypadku pierwszym robisz tak samo.

Żeby ograniczyć wyniki trzeba pobrać informacje z rekordu w tabeli w której są teksty do rozbicia... Sprowadzi się to obliczeń na zbiorze kartezjańskim. W przypadku split rzecz się dzieje jedynie na zbiorze wejściowym. Naprawdę nie widzę tu zastosowania dla złączenia...
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

Problem z UPDATE i CONCAT()




Wyślij zaproszenie do
Anuluj