Substring wieder auf UTF8 umstellen

C

cetusa

Benutzer
Beiträge
8
Hallo liebe Community,
ich bin gerade dabei eine Tabelle mittels UPDATE Befehl zu aktualisieren, wobei mittels Substring aus einer anderen Tabelle auch ein Textabschnitt ausgelesen wird. Der Befehl funktioniert soweit auch ganz gut. Einziges Problem sind die Umlaute, welche beim Update nicht berücksichtigt werden. Wie ich nun herausgefunden habe, wird aus irgendwelchen Gründen, der Substring ins Format latina1 umgeschrieben, obwohl die dazugehörige Spalte der Tabelle (aktion) utf8 voreingestellt hat. Anbei erst mal der Code zum Updaten.
SQL: 
update vms_vertrag_datei d
inner join vms_vertrag_verlauf v ON d.vertrag = v.vertrag
SET  d.nutzer = v.nutzer, d.uploaddatum= v.timestamp
WHERE d.filename in (SELECT DISTINCT SUBSTRING(v.aktion,LOCATE('"',v.aktion)+1,(((LENGTH(v.aktion))-LOCATE('"', REVERSE(v.aktion))-1)-LOCATE('"',v.aktion)))FROM vms_vertrag_verlauf v)
AND v.aktion like 'Datei%hinzugefügt';
Weiß jemand, wie ich jetzt auch noch Text mit Umlauten berücksichtigen kann? Bin gerade nach längerer Online-Recherche etwas am verzweifeln.
 
Werbung:
Ich sehe im Statement keine Tabelle Aktion.
Statt dessen eine Spalte Aktion, die offenbar aus der Tabelle vms_vertrag_verlauf kommt.

Also welchen Typ, Collation hat die Spalte Aktion in der Tabelle vms_vertrag_verlauf kommt. Bitte keine Screenshots, einfach Create Statement pasten und oder sicherheitshalber die Table Description:
SHOW FULL COLUMNS FROM vms_vertrag_verlauf

BTW: Table Aliase sind super, aber können sich dann auch gerne unterscheiden, damit man ein Statement besser lesen kann.
 
Hallo @dabadepdu , hier der CREATE CODE:
SQL: 
CREATE TABLE `vms_vertrag_verlauf` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `vertrag` INT(11) NOT NULL,
    `timestamp` DATETIME NOT NULL DEFAULT current_timestamp(),
    `nutzer` INT(11) NOT NULL,
    `aktion` TEXT NOT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3738
;
 
Welche DBVersion nutzt Du und welchen Client und wie geht der Client mit locales um?
Wie hast Du "herausgefunden", was Du schreibst?
Was genau passiert?
Was ist erwünscht?
Wie ist die Datei codiert, in der das obige Statement steht?
Und nebenbei: Was soll das Subselect bewirken? (Tut es das, was Du erwartest?)
 
Ja der Substring tut, was ich erwarte. Die Tabelle vms_vertrag_verlauf, ist wie eine log Datei anzusehen. Der Substring bewirkt, dass aus der Spalte aktion wo drinne steht: Datei "Muster.pdf" hinzugefügt alleinig Muster.pdf extrahiert wird. Wie gesagt, so lange keine Umlaute vorhanden sind klappt alles ohne Probleme. Habe ich jedoch eine Datei, wie MüllerVertrag.pdf erfolgt kein Vergleich mit der Spalte filename aus vms_vertrag_datei. In der Tabelle vms_vertrag_datei steht dann an der Stelle für Nutzer und Uploaddatum (NULL). Als Client nutze ich HeidiSQL. Aktuell hat die Datenbank die Version 10.4.22 von Maria DB. Codiert ist das ganze eigentlich in UTF8_general_ci . Habe dass dann aber mit dem Substring aber herausgefunden, nachdem ich am Ende COLLATE utf8_general_ci eingegeben habe. Da kam dann so eine Fehlermeldung nach dem Motto, dass utf8mb4 nicht mit latin1 kompatibel sei.

Ich hoffe es hilft etwas weiter.
 
Er tut was Du erwartest oder Du erwartest, dass er tut, was er soll?
cetusa schrieb:
Habe ich jedoch eine Datei, wie MüllerVertrag.pdf erfolgt kein Vergleich mit der Spalte filename aus vms_vertrag_datei
Zum Vergrößern anklicken....
Das ist nicht sauber formuliert, der Vergleich erfolgt mit Sicherheit. Aber Du meinst wahrscheinlich, dass es ungleich ist, weil keine Datensätze angezeigt werden?
Hast Du mal geprüft, was genau bei diesem komplizierten Entfernen der Anführungszeichen geschieht?

Code: 
SELECT DISTINCT SUBSTRING(v.aktion,LOCATE('"',v.aktion)+1,(((LENGTH(v.aktion))-LOCATE('"', REVERSE(v.aktion))-1)-LOCATE('"',v.aktion))) 
   FROM (select 'my first Date' as aktion) v


Code: 
SELECT DISTINCT SUBSTRING(v.aktion,LOCATE('"',v.aktion)+1,(((LENGTH(v.aktion))-LOCATE('"', REVERSE(v.aktion))-1)-LOCATE('"',v.aktion))) 
   FROM (select '"my first Date"' as aktion) v


Code: 
SELECT DISTINCT SUBSTRING(v.aktion,LOCATE('"',v.aktion)+1,(((LENGTH(v.aktion))-LOCATE('"', REVERSE(v.aktion))-1)-LOCATE('"',v.aktion))) 
   FROM (select '"my first Dät"' as aktion) v

Wieso entfernst Du Hochkomma nicht mit replace?
 
dabadepdu schrieb:
Er tut was Du erwartest oder Du erwartest, dass er tut, was er soll?
Zum Vergrößern anklicken....
Nein an dieser Stelle tut er nicht, was ich möchte. Habe mal all deine Testbeispiele durchprobiert. Komischerweise funktioniert es bei allen 3 Optionen, das Hochkommas entfernt werden. Das umständliche Entfernen, wurde mir auf mehreren Seiten nahe gelegt, wo Ich gegooglet habe wie man den Dateinamen in etwa rausextrahiert. Da ja Datei "Dateiname.endung" hinzugefügt in der Tabelle steht.
 
Ja, komisch? Bei mir entfernt der Ausdruck auch die Hochkomma, aber nicht nur die. Also könnte man sagen, er tut was er soll?

Ist Dir klar, wie UTF8 funktioniert und was der Code tut, der Dir "empfohlen" wurde?

Code: 
select length("a");

vergleiche
Code: 
select length("ä");

MariaDB 10.4

| version() | | :---------| | 10.4.22-MariaDB |
dbfiddle.uk dbfiddle.uk

hast Du replace probiert?
Code: 
select replace('"mein blöder Dateiname.txt"','"','')

Wie ist die Default Collation ?
SELECT @@collation_server;
SHOW SESSION VARIABLES LIKE 'collation%';
 
Mit UTF8 wechselt die Codierung von single byte zu multi byte. Ab da wird es unbequem, Texte mittels Längenangaben zu untersuchen bzw. zu ändern, die byte bezogen sind. Denn ein Byte ist dann nicht mehr gleich ein Zeichen.

[Edit]
Hierfür gibt es extra Funktionen, die explizit Zeichen orientiert arbeiten. Das Verhalten hängt auch mit DB oder Session Einstellungen zusammen und kann entsprechend variieren.
 
Guten Morgen, habe das jetzt mal getestet mit dem Replace. An sich funktioniert das mit dem drum herum entfernen ja ganz gut bei einer normalen Select Abfrage die nur von der Tabelle verlauf kommt. Mache ich nun aber eine Select Abfrage in Kombination mit der Tabelle datei bekomme ich keine Ergebnisse. Wiederhole ich das ganze aber statt mit REPLACE, mit meinem Substring in der WHERE Bedingung werden mir alle Ergebnisse angezeigt (bis auf Namen, wo Umlaute enthalten sind).

Vergleich:
SELECT-Abfrage mit REPLACE:
SQL: 
SELECT filename, d.vertrag FROM vms_vertrag_datei d
INNER JOIN vms_vertrag_verlauf v ON d.vertrag = v.vertrag
WHERE d.filename IN (select REPLACE(REPLACE(REPLACE(v.aktion,'"',''),'Datei',''),'hinzugefügt',''))
;

(gleiche) SELECT-Abfrage mit SUBSTRING:
SQL: 
SELECT filename, d.vertrag FROM vms_vertrag_datei d
INNER JOIN vms_vertrag_verlauf v ON d.vertrag = v.vertrag
WHERE d.filename IN
(SELECT DISTINCT SUBSTRING(v.aktion,LOCATE('"',v.aktion)+1,(((LENGTH(v.aktion))-LOCATE('"', REVERSE(v.aktion))-1)-LOCATE('"',v.aktion))))
;

Habe ich irgend etwas in der Abfrage eventuell bei Replace falsch formuliert, denn bei nachfolgender Abfrage bekomme ich ja Ergebnisse.
SQL: 
select replace(replace(REPLACE(v.aktion,'"',''),'Datei',''),'hinzugefügt','') AS Datei FROM vms_vertrag_verlauf v WHERE aktion LIKE 'Datei%hinzugefügt'
 
Schwer verständlich was Du schreibst. Welcher Satz gehört zu welchem Statement?
"Funktioniert ganz gut", "bekomme Ergebnisse" sind keine hilfreichen Aussagen.
Ein Vergleich besteht immer aus 2 Seiten bzw. Werten. Ein Vergleich der keine Ergebnisse liefert, kann auf der einen oder anderen Seite fehlerhaft sein.
Ohne tatsächlich vorliegende Echt-Daten, kann man da keine Aussagen treffen.

Es ist üblich, dass Testdaten mitgeliefert werden, sowie konkrete Ergebnisse aus erfolgreichen oder nicht erfolgreichen Statements.
Um die unpassenden Werte zu lokalisieren, kann man z.B. ein Statement schreiben, das nebeneinander original Spaltenwert sowie den Ausdruck aus dem Vergleich in der Where Condition darstellt.
 
Werbung:
Vielen Dank für deine Mithilfe @dabadepdu ! Wir haben zwar aneinander vorbei geschrieben und es so zu Missverständnissen kommen lassen, im Endeffekt habe Ich aber eine Lösung für mein Problem gefunden:
SQL: 
update vms_vertrag_datei d
inner join vms_vertrag_verlauf v ON d.vertrag = v.vertrag
SET  d.nutzer = v.nutzer, d.uploaddatum= v.timestamp
WHERE d.filename in (SELECT substring_index(substring_index(v.aktion,'"',2),'"',-1)
FROM vms_vertrag_verlauf v)
AND v.aktion like 'Datei%hinzugefügt';
 
Zurück
Oben