Anfänger / Problem bei Zeichenfolgen

Hallo zusammen,

erst mal von mir ein Hallo:
Ich bin neu hier und beschäftige mich seit ein paar Wochen mit PostreSQL und bekomme auch schon die ersten Abfragen erfolgreich hin.
Ich nutze den pgAdmin umd eine Abfrage von unserem SQL Server zu erstellen, welche dann in VBA Code oder Excel-Dateien weiter verwendet wird.
Bis jetzt war es in der ersten Linie einfache Abfragen mit Select, und where usw.

Bei meinem Problem handelt es sich um folgende Aufgabe:
Vorhanden ist eine Datenbank, die Daten zu Projekten enthält. Jede Zeile hat also eine Projektnummer, die fortlaufend ist.
Die Projektnummer sieht z.B. so aus: 17-0123.
Es kommt aber gelegentlich vor, dass mal Lücken vorhanden sind, nach 0123 also z.B. 0125 kommt.
Diese Fälle soll ich kennlich machen indem z.B. zusätzlich eine leere Zeile eingefügt wird.

Mein Ansatz ist hier eine “Dummy-Tabelle” zu erzeugen, die mir Projektnummern fortlaufend erzeugt.
Wenn ich diese Tabelle habe muss ich sie mit dem Original vergleichen und mit einem left Join die leeren Zeilen erzeugen (evtl. kann man das ja irgendwie auf eine Zeile je Lücke begrenzen)

Meine (Anfänger) Probleme sind jetzt:

  1. Die Projektnummer ist leider nicht ganz so einheitlich wie hier dargestellt. Manchmal gibt es noch Buchstaben zwischen denbeiden Zahlenreichen oder dahinter. Frage: Gibt es eine Möglichkeit in SQL alles was ein Buchstabe ist aus einem String zu entfernen?
  2. Wie erzeuge ich die nötige Zeichenfolge? Mittels generate_series kann ich zwar Zahlenfolgen erzeugen aber das genügt nicht ganz für meine Projektnummern.
    Bisher habe ich viel mit VBA gearbeitet, wo ich solche Folgen mit Schleifen erzeugen würde:
    for i=10 to 20
    for j=0 to 999
    strProjektnummer(k)=i & “-” & j
    k=k+1
    next j
    next i

Wäre Super wenn ihr mir hier Tipps geben könntest. Vielleicht mache ich es mir hier auch etwas zu umständlich…

Gruß
Andreas

1.)

test=*# select regexp_replace('123abc456XYZ','[^0-9]','','g');
 regexp_replace 
----------------
 123456
(1 Zeile)

2.)

test=*# select to_char(x,'09') || '-' || to_char(y,'FM009') from generate_series(10,12)x cross join (select y from generate_series(0,15)y) y;
 ?column? 
----------
  10-000
  10-001
  10-002
  10-003
  10-004
  10-005
  10-006
  10-007
  10-008
  10-009
  10-010
  10-011
  10-012
  10-013
  10-014
  10-015
  11-000
  11-001
  11-002
  11-003
  11-004
  11-005
  11-006
  11-007
  11-008
  11-009
  11-010
  11-011
  11-012
  11-013
  11-014
  11-015
  12-000
  12-001
  12-002
  12-003
  12-004
  12-005
  12-006
  12-007
  12-008
  12-009
  12-010
  12-011
  12-012
  12-013
  12-014
  12-015
(48 Zeilen)

Hallo akretschmer,

vielen Dank für deine Antwort.
Habe dein Beispiel eingebaut und es funktioniert auch wie erwartet :slight_smile:

Nun bin ich dabei und möchte die mehrfach vorhandenen leeren Zeilen gegen eine ersetzten.
Mein Gedanke wäre die nicht benötigten Zeilen in meinem Ergebnis zu löschen.
Wieder ein Beispiel aus VBA:
for i=1 to 100
if Zeile(i-1)<>"" and Zeile(i+1)="" then
’ Zeile löschen
end if
next i
Leider weiß ich nicht wie ich das bei einem SQL Aufruf mache bzw. ob das überhaupt geht?
Kannst du mir hier evtl. noch einen Tipp geben oder war meine Herangehensweise evtl. ungünstig?

Danke schon mal.

Gruß
Andreas

Vielleicht sowas in der Art. Ich erstelle mal eine Tabelle:

test=# create table demo_table as select s, repeat('x', (random()*10)::int-2) from generate_Series(1,10) s;
SELECT 10
test=# select * from demo_table ;
 s  | repeat  
----+---------
  1 | 
  2 | 
  3 | xxxxx
  4 | xxxxxxx
  5 | 
  6 | x
  7 | xxxxxxx
  8 | 
  9 | xxxx
 10 | xxx
(10 Zeilen)

Die Zeile mit id=2 soll also nicht gezeigt werden, oder?

test=# select *, case when lag(repeat) over (order by s) = repeat  and repeat = '' then 'x' else '' end from demo_table ;
 s  | repeat  | case 
----+---------+------
  1 |         | 
  2 |         | x
  3 | xxxxx   | 
  4 | xxxxxxx | 
  5 |         | 
  6 | x       | 
  7 | xxxxxxx | 
  8 |         | 
  9 | xxxx    | 
 10 | xxx     | 
(10 Zeilen)

test=# select s, repeat from (select *, case when lag(repeat) over (order by s) = repeat  and repeat = '' then 'x' else '' end as c from demo_table) foo where c != 'x' ;
 s  | repeat  
----+---------
  1 | 
  3 | xxxxx
  4 | xxxxxxx
  5 | 
  6 | x
  7 | xxxxxxx
  8 | 
  9 | xxxx
 10 | xxx
(9 Zeilen)

Moin,
das scheint genau das, was ich brauche.

Ich habe einen Teil deines Code mal in den pgAdmin gehauen:

select 
	*,
	case when lag(Tab1.repeat) over (order by 1) = Tab1.repeat  and Tab1.repeat = '' then 'x' else '' end 
from 
(	
	select 
		repeat('x', (random()*10)::int-2) 
	from 
		generate_Series(1,10) s
)Tab1

Da funktioniert er auch wie hier von dir gezeigt.
Wenn ich das aber in meinen eigenen Code überführen schaffe ich es nicht das Ganze zum laufen zu bekommen.

Ich habe meinen Code mal beigefügt und das aus meiner Sicht unwesendliche entfernt:

select 
	*,
	case when lag(Tab1."Projekt") over (order by 1) = Tab1."Projekt"  and Tab1."Projekt" = '' then 'x' else '' end 
from 
(
	( -- Dummy-Tabelle generiert Zahlenfolge für Projektnummer fortlaufend 
		select 
			cast(to_char(x,'09') || '' || to_char(y,'FM0009') as numeric) as "DummyListe"
		from 
			generate_series(12,12)x 
			cross join (select y from generate_series(0,9999)y) y
	) DummyTabelle
	left join
	( -- Ergebnisdaten-Tabelle
		select 
			projekt."Projekt",
			projekt."Name",
			abteilung."Abteilung" as "Abteilung (Projekt)",
			case when mitarbeiter."Mitarbeiter" is null then '-' else mitarbeiter."Mitarbeiter" end as "Mitarbeiter",
			projekt."Status",
			1 as "Dummy",
			cast(regexp_replace(projekt."Projekt",'[^0-9]','','g')as numeric) as "PrOhneZeichen"
		from 
		( 
		-- Projekt-Tabelle erzeugt Spalten mit Projektdaten
		) projekt
		,
		( 
		-- Mitarbeiter-Tabelle erzeugt Spalten mit Mitarbeiterdaten
		) mitarbeiter
		,
		( 
		-- Abteilung-Tabelle erzeugt Spalten zur Abteilung
		) abteilung
	where projekt.id=abteilung.id
	and projekt.id=mitarbeiter.id
	order by "Projekt"
	) ErgebnisDaten
	on ErgebnisDaten."PrOhneZeichen"  = DummyTabelle."DummyListe"
)Tab1

Die Tabelle wird wie gehabt erstellt aber es werden keine “x” eingetragen.
Hast du eine Idee wo der Fehler liegen könnte?

Danke noch mal für die Hilfe.

Hallo noch mal,
ich weiß zwar nicht warum aber es lag an den leeren Zellen:
Zwei leere Zellen waren warum auch immer nicht gleich.
Erst ein “IS NULL” hat abhilfe geschafft:

case when lag(Tab1."Projekt") over (order by 1) is null and Tab1."Projekt" is null then 'x' end as ZeilenFiler

Anschließend habe ich die leeren Zeilen des Zeilenfilters weg gelassen und bin so gut wie fertig :slight_smile:
Danke noch mal für die Hilfe.

Ah ja. NULL und ‘’ sind zwei unterschieliche Dinge.

postgres=> select '' = '';
 ?column? 
----------
 t
(1 Zeile)

postgres=*> select '' = NULL;
 ?column? 
----------
 
(1 Zeile)

postgres=*> select '' || 'x';
 ?column? 
----------
 x
(1 Zeile)

postgres=*> select NULL || 'x';
 ?column? 
----------
 
(1 Zeile)

postgres=*>

Also, immer schön aufpassen und NULL von ‘’ unterscheiden.

Hallo noch mal =)

Mein ToDo ist fast fertig.
Ich habe nur noch ein Problem:
Und zwar soll die Projektnummer erweitert werden.
Also z.B. 17-ABC-1234 und 17-IO-1234
Das Problem ist, dass es weiterhin Projektnummern mit dem Muster 12-1234 gibt.

Die ersten Muster kann ich super mit dem Like-Operator abfangen und mit UNION dann die Ergebnisse hintereinander schalten:

case when p.number LIKE '__-ABC-____%' then p.number end

Ich mache das im Select Statement, was auch gut funktioniert.

Wenn der LIKE Operator zw. Buchstaben und Zahlen unterscheiden würde, dann hätte ich es so gemacht (Z als Zahl):

case when p.number LIKE 'ZZ-ZZZZ%' then p.number end

Kann LIKE wirklich zwischen Zahlen nicht unterscheiden bzw. gibt es eine andere Möglichkeit?

Danke schon mal.

du kannst mit regulären Ausdrücken arbeiten.

https://www.postgresql.org/docs/10/static/functions-matching.html#functions-posix-regexp

Danke aber da hab ich so schnell nicht durchgeblickt :slight_smile:
Hab stattdessen das Ganze über einen alternativen Weg gelöst weil ich die Projekte anhand des Jahres filtern kann.

Trotzdem danke :slight_smile: