jabafi
Anmeldungsdatum: 22. Mai 2017
Beiträge: Zähle...
|
Liebe Community, ich bräuchte heute eure Hilfe ☺ damit ich mein Problem nicht immer umständlich über REPLACE und split löse.. die Frage wer mir kurz und bündig folgendes Problem erklären könnte. SQL: MariaDB String kann folgendes Format besitzen
(String liegt QUOTE vor..) |
- null
- ''
- ' 123,333'
- ' 222.2222,333'
- ' 2'
- '2,3'
|
hierbei handelt es sich um eine Deutsche Formatierung (tausend mit "." getrennt; dezimal mit ",")
der String müsste auf DECIMAL gecastet werden und mittels REGEXP_REPLACE auf eine engl. dezimal (Trennzeichen ".") formatiert werden. (DEZIMAL mit 3 zeichen nach dem .) Ich wäre über Lösungsvorschläge dankbar mfg Jabafi
|
frostschutz
Anmeldungsdatum: 18. November 2010
Beiträge: 7657
|
Ist das nicht einfach: Punkt zu nix, Komma zu Punkt? (In dieser Reihenfolge.) Dann wäre Regexp das falsche Werkzeug. Mit führenden Leerzeichen scheint DECIMAL klarzukommen, ansonsten auch noch Leerzeichen zu nix. SELECT CONVERT(REPLACE(REPLACE(' 222.2222,333','.',''),',','.'), DECIMAL(30,3))
2222222.333
1 row
|
rklm
Projektleitung
Anmeldungsdatum: 16. Oktober 2011
Beiträge: 12823
|
jabafi schrieb:
Ich wäre über Lösungsvorschläge dankbar
Wenn ich nur wüsste. für welches Problem... Liegen die Strings in der Datenbank oder außerhalb? Sollen die Zeichenketten in der DB geändert werden? Oder sollen sie ausgelesen und ausgegeben werden? Oder sollen sie in eine andere Spalte eingefügt werden? Wenn sie in der DB liegen, warum sind überhaupt in einem Textfeld Zahlen gespeichert?
|
jabafi
(Themenstarter)
Anmeldungsdatum: 22. Mai 2017
Beiträge: 6
|
@frostschutz vielen Dank für dein Lösungsvorschlag ☺
und ja so funktioniert es. Nur wenn jetzt der Fall Auftritt das irgendein Benutzer falsche Daten hinterlegt hat; d.h. hier noch Buchstaben enthalten sind hab ich ein Problem @rklm also ich hole mir aus einer TXT frische Daten (ist ein Export aus XY Database) | LOAD DATA INFILE 'export.txt'
INTO TABLE new_table
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
|
die Tabelle hat ist hier nur zum reinschmeissen gedacht. Die Datenbank erledigt hier dann den Rest...
also es liegen alle Daten im String Format vor | - null
- ''
- ' 123,333'
- ' 222.2222,333'
- ' 2'
- '2,3'
|
hier sollen aber Buchstaben die enthalten sein könnten... entfernt werden Verfahren:
- per Trigger bzw. Call wird die Row geholt, und jede Spalte nach den bestimmten Kriterien konvertiert - und in die MAIN Table kopiert.
|
frostschutz
Anmeldungsdatum: 18. November 2010
Beiträge: 7657
|
Jetzt also auch noch Buchstaben... du hast ja mal genaue Anforderungen. 😉 Und was passiert dann damit? Ungültig also 0? Oder Buchstaben entfernen und die übrig bleibenden Zahlen trotzdem interpretieren? Ist '2x2' dann 2 oder 22? Fragen über Fragen... Römische Zahlen sind wenigstens nicht dabei? 😇
|
jabafi
(Themenstarter)
Anmeldungsdatum: 22. Mai 2017
Beiträge: 6
|
@frostschutz 😛 wir wollen es ja nicht zu einfach haben - also bei buchstaben - Buchstaben entfernen + leerzeichen
- restliche zahlen interpretieren
| - 1 --> 1.000
- 1,3 --> 1.300 <-- SQL CAST DECIMAL mit 30,3
- 1,4000 --> 1.400
- 1,3434 --> die kann auch in SQL gespeichert werden wird nur nicht 4 stellig angezeigt
- 13.222,333 --> 13222.333
- 12.222 --> 13222.000
- ss12.000,000 --> 12000.000
- wenn nichts interpretierbar ist 0.000 setzen
|
|
frostschutz
Anmeldungsdatum: 18. November 2010
Beiträge: 7657
|
Also das Beispiel jetzt wäre z.B. ^[^0-9]+ durch nix. Das würde Buchstaben usw. am Anfang wegradieren. Viel Glück.
|
jabafi
(Themenstarter)
Anmeldungsdatum: 22. Mai 2017
Beiträge: 6
|
| SELECT REGEXP_REPLACE('wqwqr12.333,22', '^[^0-9]+' ,'');
'12.333,22'
SELECT CONVERT(REGEXP_REPLACE('wqwqr12.333,22', '^[^0-9]+' ,''), DECIMAL(30,3));
'12.333'
|
Leider nicht ganz;
|
frostschutz
Anmeldungsdatum: 18. November 2010
Beiträge: 7657
|
Das natürlich zusätzlich zu dem oben.
|
rklm
Projektleitung
Anmeldungsdatum: 16. Oktober 2011
Beiträge: 12823
|
jabafi schrieb:
die Tabelle hat ist hier nur zum reinschmeissen gedacht. Die Datenbank erledigt hier dann den Rest...
also es liegen alle Daten im String Format vor
Ich finde Skriptsprachen wie Ruby oder Python deutlich besser geeignet um Text in dieser Art und Weise zu verarbeiten. Außerdem hast Du in der DB immer den Overhead für das Transaktionsmanagement.
| - null
- ''
- ' 123,333'
- ' 222.2222,333'
- ' 2'
- '2,3'
|
hier sollen aber Buchstaben die enthalten sein könnten... entfernt werden
Es ist immer noch nicht ganz klar, was die möglichen Eingaben und was die möglichen Ausgaben sind.
- per Trigger bzw. Call wird die Row geholt, und jede Spalte nach den bestimmten Kriterien konvertiert - und in die MAIN Table kopiert.
Ich finde, Du erzeugst eine Menge unnötigen IO und Overhead. Ich würde das eher so machen, dass ich die Daten lese (Skripsprachen sind toll für so etwas), konvertiert ausgebe und dann gleich diesen Strom in die Datenbank lade. Alternativ kann man den konvertierten Datensatz auf die Platte schreiben und dann mit LOAD DATA effizient in die DB laden.
|
seahawk1986
Anmeldungsdatum: 27. Oktober 2006
Beiträge: 11179
Wohnort: München
|
Wäre es nicht besser schon bei der Eingabe der Daten durch den Nutzer zu überprüfen, ob es ungültige Zeichen gibt? Dann kann man sich das spätere Aufräumen sparen.
|
rklm
Projektleitung
Anmeldungsdatum: 16. Oktober 2011
Beiträge: 12823
|
seahawk1986 schrieb: Wäre es nicht besser schon bei der Eingabe der Daten durch den Nutzer zu überprüfen, ob es ungültige Zeichen gibt? Dann kann man sich das spätere Aufräumen sparen.
Klar: je früher, desto besser. Wir kennen ja die Quelle der Daten nicht; möglicherweise kann man dort keine solche Validierung machen. Generell ist es schlecht, dass Zahlenwerte überhaupt so abgespeichert werden. Man sollte schon eine maschinenlesbare Form wählen. Das hindert ja nicht, dass die Daten bei der Anzeige für die entsprechende Locale des aktuellen Betrachters aufbereitet werden.
|
jabafi
(Themenstarter)
Anmeldungsdatum: 22. Mai 2017
Beiträge: 6
|
klar sinnvoll wäre eine Überprüfung bei der Eingabe; Problem an der Sache,... ich bekomme nur ein txt bzw. xls export (aus SAP heraus) ...somit muss man leider selber alles validieren. Ich teste mal noch rum...ob ich eine gescheite Abfrage hinbekomme. @all
Danke
|
jabafi
(Themenstarter)
Anmeldungsdatum: 22. Mai 2017
Beiträge: 6
|
was haltet ihr von dieser Lösung? 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31 |
SELECT REGEXP_REPLACE('wqwqr12.333,22', '[^0-9/,]' ,'');
+--------------------------------------------------+
| REGEXP_REPLACE('wqwqr12.333,22', '[^0-9/,]' ,'') |
+--------------------------------------------------+
| 12333,22 |
+--------------------------------------------------+
1 row in set (0.00 sec)
#################################################################
SELECT REGEXP_REPLACE('wqwqr12.333dh', '[^0-9/,]' ,'');
+-------------------------------------------------+
| REGEXP_REPLACE('wqwqr12.333dh', '[^0-9/,]' ,'') |
+-------------------------------------------------+
| 12333 |
+-------------------------------------------------+
1 row in set (0.00 sec)
#################################
SELECT REGEXP_REPLACE(' 4', '[^0-9/,]' ,'');
+---------------------------------------+
| REGEXP_REPLACE(' 4', '[^0-9/,]' ,'') |
+---------------------------------------+
| 4 |
+---------------------------------------+
1 row in set (0.00 sec)
|
|
rklm
Projektleitung
Anmeldungsdatum: 16. Oktober 2011
Beiträge: 12823
|
jabafi schrieb: was haltet ihr von dieser Lösung?
Ich hatte ja oben bereits erwähnt, dass ich das nicht in der DB machen würde. Davon abgesehen kennen wir ja immer noch nicht die möglichen Eingaben und wie sie in gewünschte Ausgaben transformiert werden sollen. Deine Lösung macht z.B. aus "2 3" "23", aber will man das? Oder sollte das vielleicht eher als Fehler geflaggt werden?
|