String bei jedem Komma oder Semikolon trennen und Inhalt in neue Spalte schreiben

Hallo ich habe folgendes Problem.

Ich habe eine Tabelle mit einer Spalte “Annotation”. Eine Zeile daraus sieht ungefähr so aus:

INCREASE_UNLOCKSbyStacey(2011-03-28);INCREASE_UNLOCKSbyStacey(2011-03-28),INCREASE_UNLOCKSbyStacey(2011-03-28)

Diesen Wirrwarr möchte ich jetzt mit einer Abfrage bei jedem KOMMA und jedem SEMIKOLON trennen und den Inhalt jeweils in eine neue Spalte in der selbe Zeile schreiben.

Ich möchte quasi das erreichen, was ich in Excel über die Funktion “Text in Spalten” erzielen kann.

Wie realisiere ich das in postgre SQL?

Ich benutze übrigens nicht das pstgre SQL plugin sondern den ganz normalen Abfragegenerator.

lG
Stefan

Die Anzahl der durch Komma oder Semikolon getrennten ‘Felder’ ist variabel, also in jeder Zeile unterschiedlich, ja? Damit würden dann je Zeile unterschiedlich viele Spalten entstehen, ja?

Falls ja: vergiß es, Du wirst so nicht weiterkommen.


Andreas

Ja ist unterschiedlich…

Ja, komme ich ja auch nicht, deshalb frag ich doch, wie es geht.

WIe soll ich es denn sonst machen?

Gibt es denn per SQL keine Möglichkeit exakt das gleiche zu machen, was Excel über die Funktion “Text in Spalten” veranstaltet??

lG

Alternativ:

Eine SQL-Funktion die folgende Funktion aus Excel ersetzt würde ja schon reichen, oder?:

=WENN(LÄNGE(Annotation)>1;–TEIL(Annotation;SUCHEN(";";Annotation)+1;10);"")

Bekomme das nur für das erste Komma auf die Reihe:

SELECT
annotation,
strpos(annotation,’,’),
rpad(annotation, strpos(annotation,’,’)-1),

Weiter weiß ich nicht, da ich weder die Funktion Rechts oder Links in postgre SQL finde, noch weiß wie das überhaupt gehen soll, dass er jetzt das nächste Komma oder Semikolon sucht und diesen Inhalt dann in eine neue Spalte packt.

Kann mir da jemand helfen??

Was Du meiner Meinung nach brauchst ist eine Funktion, die in einer Schleife durch Deine jetzige Tabelle durchgeht und die Zerlegung macht. Diese Funktion erstellt dann eine Tabelle in einem normalisierten Datenmodell.

Ich hab jetzt grad weder Zeit noch Lust, Dir das zu machen. Im netz findest Du aber massig Beispiele für sowas, schon in der Doku zu PostgreSQL / plpgsql.


Andreas

Exakt. Genau sowas benötige ich.

Kannst du mir vielleicht noch einen Hinweis geben unter was ich da genau schauen muss? Habe bisher dort noch nicht mal eine Funktion LEFT oder RIGHT gefunden.??

split_part z.B., PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: String Functions and Operators

Andreas

MIt split_part hab ich auch schon versucht:
split_part(annotation, ‘,’,1),

Damit bekomme ich halt alles Links vom ersten Komma in eine Spalte. Die Schleife, die ich brauche um durch alles zu iterieren, ist allerdings etwas von dem ich absolut keine Ahnung habe.

Muss ich mir wohl irgendwie externe Hilfe holen oder gucken ob das hier irgendjemand weiß. Ich hab sonst eig nur mit Excel zu tun und brauche SQL nur für normale Abfragen aber nicht um dort komplexe Formelarbeiten aus Excel zu erledigen.

Alles ein mist…

Stell Dich nicht so an! :wink:

test=*# select * from foo;
 i |       t
---+----------------
 1 | eins
 2 | eins,zwei,drei
 3 | drei
 4 | drei,vier
(4 rows)

Mal als Ausgangstabelle. Nun eine simple Funktion:

create or replace function my_foo(out i int, out t text) returns setof record as $$
declare
  r record;
  c int;
begin
  for r in select * from foo loop
    i:=r.i;
    c:= length(r.t) - length(replace(r.t,',',''));
    while (c >= 0 ) loop
      t:=split_part(r.t,',',c+1);
      c:=c-1;
      return next;
    end loop;
  end loop;
end;
$$ language plpgsql;

ergibt:

test=*# select * from my_foo();
 i |  t
---+------
 1 | eins
 2 | drei
 2 | zwei
 2 | eins
 3 | drei
 4 | vier
 4 | drei
(7 rows)

So, das baust halt aus, bis das auf Deinen Zweck paßt.

Oder: hostest das bei uns und gibst es mir in Auftrag :wink:

Andreas

Warum so kompliziert?

select unnest(string_to_array(t,',')) from foo

Hallo euch beiden

Danke Andreas, werde mir das morgen mal anschauen auf Arbeit und schauen ob ich damit was hinbekomme. Glaube allerdings nicht, da ich deinen Code nicht in den Abfrageeditor Code umwandeln kann, benutze ja nicht dieses psql interface…

@castorp

Danke dafür aber was genau ist “unnest” und wo in der formel muss meine Quellspalte (annotation) rein? Mit der Abfrage geht der mir doch abern icht jedes Komma entlang und schreibt die Inhalte zwischen zwei Kommata in separate Spalten?

@castorp: jupps, recht haste :wink:
@coaler24: ob Du psql nutzt oder das via PHP oder PGAdmin oder PhpPgAdmin oder sonstwie der DB vorwirfst ist völlig egal. Und unnest() ist halt eine Funktion, PostgreSQL: Documentation: Manuals: PostgreSQL 9.1: Array Functions and Operators.

Andreas

Cool. Und wo sag ich ihm auf welchen string das angewandt werden soll, also wo kommt meine Spalte annotation hin?

Welchen Part der Doku zu unnest() und string_to_array() hast Du nun nicht verstanden?

Andreas

Hmm, hatte mit arrays noch nie was zu tun und kann mir darunter auch nichts vorstellen.

Bei einer Funktion wie “strpos(string, substring)”, ist meine Annotation der string und mein “,” der substring…

Das finde ich aber bei dem unnest() und string_to_array() nirgends wieder, daher die Frage wohin das annotation muss? kann ja schlecht annotation_to_array() draus machen, oder?

Ist das echt soooo schwer? string_to_array() macht was? Es macht aus einem String ein ARRAY. Dazu teilt es den übergebenen String an dem übergebenen Trennzeichen auf und gibt ein ARRAY zurück.

unnest() macht laut Beschreibung: “expand an array to a set of rows”.

Und Klammerrechnung lernt man in der Schule.


Andreas

Ich versteh es dennoch nicht…

Ich versuche das so:
unnest(string_to_array(annotation,’,’))

Aber da sagt er mir alle möglichen Fehler wie “syntax error at or near “unnest”” und wenn ich das so mache:
unnest(string_to_array(‘annotation’,’,’))

kommt function unnest(text[]) does not exist…

Das Annotation ist vom Typ character varying (5000), falls das wichtig ist.

Welche PostgreSQL version verwendest Du denn? (Die kannst Du Dir mit select version() anzeigen lassen)