Falsche Zeichen in einem DB-Feld per SQL finden

[nachtstreuner60]

Hallo Spezialisten,

ich suche mal wieder nach einer Lösung per SQL-Befehl.

In einer Datenbank werden eingescannte(Barcodewerte) in einem VarChar-Feld abgelegt. Der String fängt immer mit
dem Buchstaben 'F' an, danach schliessen sich Ziffern an (6 stellig) z. B. F000256.

Nun erzeugt der Barcodescanner manchmal einen Fehlerhaften String z.B. 'F00/257' oder 'F0%0078', es gibt also manchmal
hinter dem ersten Buchstaben F auch andere Zeichen als nur die Ziffern 0-9 welche richtig wären.

Wie kann ich nun das Feld 'ScanCode' indem dieser String abgelegt wird nach diesen fehlerhaften Scancodes durchsuchen und
als Ergebnis einer SQL-Abfrage darstellen.

Unten habe ich mal einen aktuellen Auszug(Screen) der DB. :wall:

Danke für eure Bemühungen.

[alzaimar]

Mit 'LIKE' sollte das gehen,

SQL-Code:

Select * from tabelle where Barcode Not Like 'F[0-9][0-9][0-9][0-9][0-9][0-9]'

Speziell für Firebird müsstest du mal in der Hilfe nachschauen....

[nachtstreuner60]

Hallo alzaimar,

vielen Dank für die rasche Antwort.

Ich habe deinen Tip mal ausprobiert, erhalte aber u.a. Fehlermeldung im ScreenCopy.

Hier mal der Codeschnipsel :

Delphi-Quellcode:

procedure TForm1.Button1Click(Sender: TObject);
begin
with zConFehler do begin
     zConFehler.Connect;
     zQuery1.SQL.Clear;
     zQuery1.SQL.Text :=  'select * from IDOC_REAL '+
         'where Idoc_REAL."ScanCode" Not Like '+
         'F[0-9][0-9][0-9][0-9][0-9][0-9]';
     zQuery1.ExecSQL;
     zQuery1.Active := true;
end;

[nachtstreuner60]

Hallo alzaimar,

ich habe jetzt mal den SQL-Befehl abgeändert, jetzt bekomme ich keine Fehlermeldung mehr.
Aber das Ergebnis ist nicht richtig. Er zeigt alle Datensätze, ob fehlerhafte (wie die gelb markierten,) als auch die
richtigen und fehlerfreien Datensätze .

Screenshot anbei. Was mache ich hier noch falsch ?

Delphi-Quellcode:

procedure TForm1.Button1Click(Sender: TObject);
begin
with zConFehler do begin
     zConFehler.Connect;
     zQuery1.SQL.Clear;
     zQuery1.SQL.Text :=  'select * from IDOC_REAL where Idoc_REAL."ScanCode" '+
     'Not Like '+Quotedstr('F[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')+
     ' and AKTION = '+Quotedstr('FEHLER')+
     ' order by "ScanCode"';
     zQuery1.ExecSQL;
     zQuery1.Active := true;
     label1.Caption :=IntToStr(zQuery1.RecordCount);
end;

Hinweis : Das Feld Scancode hat eine Feldlänge von zehn Zeichen, deswegen 9x [0-9]

[Union]

Mal was ganz anderes: Können die Werte nicht vor dem Eintrag in die DB auf Fehler getestet werden? Und falls der Scanner die Daten direkt über ein Tool reinschreibt: Man kann fast alle professionellen Scanner so programmieren, dass nur Codes mit einem bestimmten Aufbau als korrekt anerkannt werden. Gerade so eine einfache Regel wie "F" gefolgt von 9 numerischen Zeichen läßt sich dann einfach erstellen.

[nachtstreuner60]

Hallo Union,
danke für den Hinweis. Das habe ich mir auch schon überlegt.

Ich habe aber nur Zugriff auf Bestandsdaten, nicht schon vor dem Scannen. Die Scanner und das Erfassungstool sind von einer Fremdfirma.

Dieses Tool nimmt den gescannten Barcode entgegen, reichert diesen mit verschiedenen Daten an (z.B. Datum, Uhrzeit,ScannerID usw.
und schreibt dann dieses in ein Textfile.

Dieses Textfile wird dnach automatisch eingelesen in gewissen Zeitabständen und dann in diese Datenbank geschrieben.

Erst hier habe ich dann eine Überprüfungsmöglichkeit.

Trotzdem vielen Dank. Ich bin mir aber sicher, das es auch in der DB eine Möglichkeit geben muss, diese einfach per SQL auszusortieren.

mfg
Nachtstreuner60

[Union]

Zitat:

Dieses Textfile wird dnach automatisch eingelesen in gewissen Zeitabständen und dann in diese Datenbank geschrieben.

Und wie wäre es hier mit einem Trigger?

[nachtstreuner60]

Dazu müsste ich aber an den Quellcode dieses Tools kommen :pale:

[Union]

Nein, den Trigger erstellst Du (die Berechtigungen vorausgesetzt) in der Datenbank. Der könnte dann die fehlerhaften einträge z.B. in eine separate Tabelle schreiben.

[nachtstreuner60]

Hallo

gibt es vielleicht eine Möglichkeit, zu prüfen ob es sich ab der zweiten Stelle, also ohne dem 'F' um eine Zahl handelt order nicht
z.B
F und ab hier 000345 = eine ZAHL
F und ab hier 00/345 = keine Zahl

Hat jemand eine Lösung hierfür ?

[mkinzler]

Wrum? Ein Trigger läuft doch in der Datenbank und reagiert auf das Schreiben durch das Programm

[nachtstreuner60]

Hallo Union,

habe noch nie mit Triggern gearbeitet, wie funktionieren die ?

Muss hierfür in der Firebird Datenbank etwas gecodet werden ?

[Union]

Ja, sowas wie:

SQL-Code:

CREATE TRIGGER IDOC_CHECK FOR IDOC_REAL
ACTIVE AFTER INSERT 
AS 
begin 
  if (INSERTING) then ...
end

[nachtstreuner60]

Hallo union,

wenn ich das richtig verstanden habe, müsste ich in dem Abschnitt

Delphi-Quellcode:

if (INSERTING) then ...

prüfen, ob die Zeichenfolge ab der zweiten Stelle keine Zahl ist ?

Aber wie sieht der Code hierzu aus ?

Wenn es sich um einen Fehlerhaften Datensatz handelt, muss dieser in eine andere tabelle geschrieben werden?
Oder reicht es aus, wenn ich in der Ursprungsdatenbank ein zusätzliches Feld wie etwa (STATUS = 'F') anlege u. dieses dann
je nach Ergebnis mit einem F fülle ?

[mkinzler]

Ich würde den Trigger nicht im AFTER INSERT sondern BEFORE INSERT machen. Dann kannst du die einzufügenden Werte noch ändern oder weitere Werte setzen (z.B. den Status)
http://udf.adhoc-data.de/dokumentati...eu_string.html

[nahpets]

Hallo,

versuch doch mal sowas:

SQL-Code:

Select *
from IDOC_REAL where
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(Idoc_REAL."ScanCode",1,''),2,''),3,''),4,''),5,''),6,''),7,''),8,''),9,''),0,'')
<> 'F'

Beim SQL-Server und Postgres funktioniert das.

Eventuell könntest Du auch noch das F mit rausfiltern und dann auf einen Leerstring abfragen.
Die Umwandlung in eine Zahl wird scheitern, da beim CAST(ScanCode as INT) beim ersten Satz, der ein ungültiges Zeichen enthält, ein Ausnahme geworfen wird. Die müsstest Du dann auch noch abfangen.

Stephan

[nachtstreuner60]

Hallo Stephan,

danke für deine Antwort. Ich steige bei diesem SQL Statement nicht durch. Kannst Du mir etwas genauer erklären, was dieses Statement macht. Insbesondere das REPLACE .

Danke schon mal

[dataspider]

Hi,

ich würde für den Test eine Procedure schreiben:

SQL-Code:

SET TERM ^ ;

CREATE PROCEDURE CHECK_BARCODE (
    barcode varchar(20))
returns (
    result char(1))
as
begin
  /* check barcode */
  result = 'F';
  if (substring(:barcode from 1 for 1) <> 'F') then
  begin
    suspend;
    exit;
  end 
  if (substring(:barcode from 2 for 1) not between '0' and '9') then
  begin
    suspend;
    exit;
  end 
  if (substring(:barcode from 3 for 1) not between '0' and '9') then
  begin
    suspend;
    exit;
  end 
  if (substring(:barcode from 4 for 1) not between '0' and '9') then
  begin
    suspend;
    exit;
  end 
  if (substring(:barcode from 5 for 1) not between '0' and '9') then
  begin
    suspend;
    exit;
  end 
  if (substring(:barcode from 6 for 1) not between '0' and '9') then
  begin
    suspend;
    exit;
  end 
  if (substring(:barcode from 7 for 1) not between '0' and '9') then
  begin
    suspend;
    exit;
  end 
  result = 'T';
  suspend;
end^
SET TERM ; ^

das where sollte dann so aussehen:

SQL-Code:

where (select result from check_barcode(ScanCode)) = 'F'

Cu, Frank

[alex517]

Hi

du könntest neben dem Feld SCANCODE ein zweites Feld z.B. "SCANCODE_CLEARED" in der Tabelle führen in der die bereinigten Scancodes stehen.
Dieses Feld "SCANCODE_CLEARED" wird immer durch einen Insert/Update-Trigger gesetzt.

SQL-Code:

CREATE OR ALTER TRIGGER SCANCODES_BI FOR SCANCODES
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_SCANCODES_ID,1);

  NEW.SCANCODE_CLEARED = 'F'||F_STRIPSTRINGHOLD( F_STRCOPY(NEW.SCANCODE, 1, 10), '0123456789');
END

F_STRIPSTRINGHOLD() und F_STRCOPY() sind Funktionen aus der FreeAdHocUDF.
Die musst du natürlich vorher einbinden.

Abfrage für fehlerhaften Sätze:

SQL-Code:

select
  S.ID,
  S.SCANCODE,
  S.SCANCODE_CLEARED
from
  SCANCODES S
where
  S.SCANCODE <> S.SCANCODE_CLEARED

Abfrage für korrekte Sätze:

SQL-Code:

select
  S.ID,
  S.SCANCODE,
  S.SCANCODE_CLEARED
from
  SCANCODES S
where
  S.SCANCODE = S.SCANCODE_CLEARED

Das geht natürlich auch ohne extra Feld nur mit einen Select-Statement.
Kommt darauf an wie oft du Werte benötigst.

SQL-Code:

select
  S.ID,
  S.SCANCODE,
  'F'||F_STRIPSTRINGHOLD( F_STRCOPY(S.SCANCODE, 1, 10), '0123456789')
from SCANCODES S
where
  S.SCANCODE <> 'F'||F_STRIPSTRINGHOLD( F_STRCOPY(S.SCANCODE, 1, 10), '0123456789')

alex

[omata]

SQL-Code:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE CLEARNUMERIC (
  INPUTSTR Varchar(100)
)
RETURNS (Result Varchar(100)) AS
BEGIN
  Result = '';
  WHILE (InputStr <> '') DO BEGIN
    IF ('0123456789' NOT LIKE '%' || SUBSTRING(InputStr FROM 1 FOR 1) || '%') THEN 
    BEGIN
      Result = Result || SUBSTRING(InputStr FROM 1 FOR 1);
    END
    InputStr = SUBSTRING(InputStr FROM 2);
  END
  SUSPEND;
END^
SET TERM ; ^

SQL-Code:

SELECT *
FROM IDOC_REAL
WHERE (SELECT result FROM CLEARNUMERIC(ScanCode)) <> 'F'

[nahpets]

Hallo,

Zitat:

Zitat von nachtstreuner60

Hallo Stephan,

danke für deine Antwort. Ich steige bei diesem SQL Statement nicht durch. Kannst Du mir etwas genauer erklären, was dieses Statement macht. Insbesondere das REPLACE .

Danke schon mal

habe das Statement gegen eine SQL-Server-Datenbank getestet und kann nicht sagen, ob es dem SQL-Standard entspricht und damit gegen jede Datenbank läuft. Mit Postgres müsste es auch funktionieren.

Zu Deiner Frage:

So, wie es im Statement aussieht ist's zugegeben ein bisserl verwirrend.

Replace ersetzt einfach eine Zeichenfolge.

Also erstmal "einfach":

SQL-Code:

replace(Idoc_REAL."ScanCode",1,'')

Hier wird in Idoc_REAL."ScanCode" die 1 durch einen Leersting ersetzt und ist damit "weg".

Das nächste Replace setzt nun auf diesem "Ergebnis" auf.

SQL-Code:

replace(replace(Idoc_REAL."ScanCode",1,''),2,'')

und damit sind die 1 und die 2 weg.

Das Spiel führen wir jetzt fort, bis alle Ziffern weg sind.
Gültige Werte beginnen mit einem "F" und dürfen nur Ziffern enthalten.
Wir machen alle Ziffern weg und es darf somit nur ein "F" übrigbleiben.

Die Abfrage auf <> 'F' liefert uns nun alle die Sätze, bei denen was anderes als ein alleinstehendes "F" übrigbleibt und dies müssten die fehlerhaften Datensätze sein.

Die Prozedure von omata macht eigentlich genau dies, sie nimmt Stück für Stück die Ziffern aus der Zeichenfolge und läßt im "Idealfall" ein "F" über.

Stephan

[omata]

Zitat:

Zitat von nahpets

Die Prozedure von omata macht eigentlich genau dies, sie nimmt Stück für Stück die Ziffern aus der Zeichenfolge und läßt im "Idealfall" ein "F" über.

:thumb:

Firebird 1.5 kennt kein REPLACE, dies muss dann über eine UDF aus einer externen DLL nachgeladen werden. Mein Ansatz benötigt keine Zusätze, sondern kommt mit dem Befehlsumfang von Firebird 1.5 aus, um das Problem zu lösen. Dein REPLACE-Ansatz ist aber genau die richtige Vorgehenweise.
Bei MySQL kann der LIKE-Operator sogar reguläre Ausdrücke. Firebird ist da aber etwas eingeschränkt, besonders in der 1.5-Version. Ab 2.1 ist sogar ein REPLACE standardmäßig vorhanden.

[mkinzler]

Und ab 2.5 reguläre Ausdrücke

[nachtstreuner60]

So da bin ich wieder,

ich werde jetzt mal alle dargebotenen Tipps ausprobieren. Ich danke euch erstmal ganz herzlich für die Tipps.

Über das Ergebnis informiere ich natürlich noch.

mfg
Nachtstreuner

PS :

Delphi-Quellcode:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE CLEARNUMERIC (
  INPUTSTR Varchar(100)
)
RETURNS (Result Varchar(100)) AS
BEGIN
  Result = '';
  WHILE (InputStr <> '') DO BEGIN
    IF ('0123456789' NOT LIKE '%' || SUBSTRING(InputStr FROM 1 FOR 1) || '%') THEN
    BEGIN
      Result = Result || SUBSTRING(InputStr FROM 1 FOR 1);
    END
    InputStr = SUBSTRING(InputStr FROM 2);
  END
  SUSPEND;
END^
SET TERM ; ^

Wo trage ich diese Prozedur ein ?

Soviel ich weiss, fangen Proceduren in Delphi mit

Procedure TForm.blaBlaBla
begin
.
.
.
end;

an.

Wie ist das mit obigen Code ?

[mkinzler]

Auf eine SP kannst du entweder wie auf ein Tabelle zugreifen oder verwendest die entsprechende Komponente deiner Zugriffsbibliothek.

[nachtstreuner60]

Hallo Stephan

also bei diesem Tipp bekomme ich folgende Fehlermeldung (Screenshot).

Delphi-Quellcode:

with zConFehler do begin
     zquery1.First;

     zQuery1.SQL.Clear;
     zquery1.sql.text := 'Select * from IDOC_REAL where '+
'replace(replace(replace(replace(replace(replace(replace(replace(replace(replace'+
'(Idoc_REAL."ScanCode",1,''),2,''),3,''),4,''),5,''),6,''),7,''),8,''),9,''),0,'')'+
' <> ''F''';
zquery1.ExecSQL;
zQuery1.Active := true;

end;

Anscheinend kennt er das <> nicht

[nahpets]

Hallo,

Zitat:

Zitat von nachtstreuner60

Anscheinend kennt er das <> nicht

nein, das sieht mir nicht so aus. Wenn Du im Screenshot genau hinschaust, so befindet sich hinter jeder Ziffer ein Komma und ein Hochkomma. Sprich: Die Zeichenfolgen sind nicht abgeschlossen. Du muss in Deinem Quelltext die Hochkommata noch richtig maskieren.

So sollte es gehen.

Delphi-Quellcode:

with zConFehler do begin 
     zquery1.First; 

     zQuery1.SQL.Clear; 
     zquery1.sql.text := 'Select * from IDOC_REAL where '+ 
'replace(replace(replace(replace(replace(replace(replace(replace(replace(replace'+ 
'(Idoc_REAL."ScanCode",1,''''),2,''''),3,''''),4,''''),5,''''),6,''''),7,''''),8,''''),9,''''),0,'''')'+ 
' <> ''F'''; 
zQuery1.Active := true; 

end;

Das zquery1.ExecSQL; ist meiner Meinung nach überflüssig. Die Abfrage wird mit zQuery1.Active := true; geöffnet. ExecSQL dient zur Ausführung von SQL-Statements, die keine Ergebnismenge liefern. Statt zQuery1.Active := true; geht auch zQuery1.Open;, das macht den Unterschied vielleicht deutlicher.

Stephan

[nachtstreuner60]

Hallo Stephan,

das mit active und open habe ich verstanden und den Test mit dem geänderten Code
durchgeführt. Allerdings wieder mit einer Fehlermeldung. Diesmal erkennt er den Befehl REPLACE nicht

Delphi-Quellcode:

     zQuery1.SQL.Clear;
     zquery1.sql.text := 'Select * from IDOC_REAL where '+
'replace(replace(replace(replace(replace(replace(replace(replace(replace(replace'+
'(Idoc_REAL."ScanCode",1,''''),2,''''),3,''''),4,''''),5,''''),6,''''),7,''''),8,''''),9,''''),0,'''')'+
' <> ''F''';
zQuery1.Active := true;

[mkinzler]

Wie omata schon erwähnt hat, brauchst du bei FB1.5 eine externe UDF-Bibliothek für diese Funktion

[nahpets]

Hallo,

Zitat:

Zitat von nachtstreuner60

Diesmal erkennt er den Befehl REPLACE nicht

das habe ich nach dem Post von omata, dass Firebird 1.5 kein Replace kennt, befürchtet. Da brauchst Du entweder eine neuere Datenbankversion oder versuchst Dich am Lösungsansatz von omata. Tut mir leid, dass Dir meine Lösung nicht weiterhilft.

Omata's Prozedur ist jedoch keine Delphi-Procedur, wie Du vermutet hast, sondern eine Datenbankprozedur. Da ich mich mit Firebird nicht auskenne, kann ich Dir nicht sagen, wie Du dort am einfachsten eine Datenbankprozedur anlegst.

Stephan

[nachtstreuner60]

Trotzdem vielen Dank für dein Bemühen Stephan,

nun versuch ich´s mal mit der Version von OMATA.

Hier weis ich allerdings noch nicht, wie ich den Code einbinde und vor allem wo

Delphi-Quellcode:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE CLEARNUMERIC (
  INPUTSTR Varchar(100)
)
RETURNS (Result Varchar(100)) AS
BEGIN
  Result = '';
  WHILE (InputStr <> '') DO BEGIN
    IF ('0123456789' NOT LIKE '%' || SUBSTRING(InputStr FROM 1 FOR 1) || '%') THEN
    BEGIN
      Result = Result || SUBSTRING(InputStr FROM 1 FOR 1);
    END
    InputStr = SUBSTRING(InputStr FROM 2);
  END
  SUSPEND;
END^
SET TERM ; ^

hat jemand eine kleine Hilfestellung für mich ?

[nahpets]

Hallo,

da hier bisher nichts gekommen ist, versuche ich es mal mit meinem "fundierten Halbwissen" ;-)

Wenn Du den Quelltext der Prozedur einer Query zuweist

Delphi-Quellcode:

zQuery1.sql.clear;
zQuery1.sql.add('SET TERM ^ ;');
zQuery1.sql.add('CREATE OR ALTER PROCEDURE CLEARNUMERIC (');
zQuery1.sql.add('  INPUTSTR Varchar(100)');
zQuery1.sql.add(')');
zQuery1.sql.add('RETURNS (Result Varchar(100)) AS');
zQuery1.sql.add('BEGIN');
zQuery1.sql.add('  Result = '''';');
zQuery1.sql.add('  WHILE (InputStr <> '''') DO BEGIN');
zQuery1.sql.add('    IF (''0123456789'' NOT LIKE ''%'' || SUBSTRING(InputStr FROM 1 FOR 1) || ''%'') THEN');
zQuery1.sql.add('    BEGIN');
zQuery1.sql.add('      Result = Result || SUBSTRING(InputStr FROM 1 FOR 1);');
zQuery1.sql.add('    END');
zQuery1.sql.add('    InputStr = SUBSTRING(InputStr FROM 2);');
zQuery1.sql.add('  END');
zQuery1.sql.add('  SUSPEND;');
zQuery1.sql.add('END^');
zQuery1.sql.add('SET TERM ; ^');

und anschließend die Query mit

Delphi-Quellcode:

zQuery1.ExecSQL;

ausführst, so hoffe ich, dass dann in der Datenbank die Prouedur angelegt wurde.
Anschließend sollte eine Abfrage unter Benutzung der Prozedur funktionieren.

SQL-Code:

SELECT *
FROM IDOC_REAL
WHERE (SELECT result FROM CLEARNUMERIC(ScanCode)) <> 'F'

So wie omata das oben bereits beschrieben hat.

Stephan