nicht angemeldet
MudGuard
TS
TS
Der Martin
robertroth
LastBoyScout
Rolf B
TS
TS
Rolf B
Der Martin
dedlfix
Rolf B
Ich Suche in einer mySQL- Tabelle über mehre Spalten hinweg:
SELECT * FROM tabelle WHERE PHONE LIKE '$search' OR MOBILE LIKE '$search' OR FAX LIKE '$search'
Nun würde ich bei einem Treffer gerne auch die Bezeichnung jener Spalte erhalten, welche letztendlich den Treffer liefert. Gibt es hierzu eine elegante Möglichkeit? Oder muss ich hierzu das Ergebnis mit anderen Mitteln erneut auswerten?
Habe schon recherchiert, aber hierzu leider keinen Anhaltspunkt gefunden.
Danke und Gruß,
LBS
Rolf B
TS
Hi,
Ich Suche in einer mySQL- Tabelle über mehre Spalten hinweg:
SELECT * FROM tabelle WHERE PHONE LIKE '$search' OR MOBILE LIKE '$search' OR FAX LIKE '$search'Nun würde ich bei einem Treffer gerne auch die Bezeichnung jener Spalte erhalten, welche letztendlich den Treffer liefert.
Wieso 'jener Spalte'? 'jene Spalten' wäre doch richtiger.
Ich kenne Leute, die haben gar keine Festnetznummer, die geben bei solchen Formularen bei PHONE und bei MOBILE ihre einzige Nummer an …
cu,
Andreas a/k/a MudGuard
Hi,
Nachtrag:
wenn nur eine der Spalten ausgegeben werden soll, die zutrifft,könnte man evtl. was mit verschachteltem if machen.
A la SELECT t.*, if (PHONE LIKE '$search','PHONE', if (MOBILE LIKE '$search), 'MOBILE', 'FAX') from tabelle t …
Brauchst Du wirklich like? Suchst Du mit Wildcards ('%' bzw. '_')? Ansonsten besser das like durch ein = ersetzen.
cu,
Andreas a/k/a MudGuard
Brauchst Du wirklich like? Suchst Du mit Wildcards ('%' bzw. '_')? Ansonsten besser das
likedurch ein=ersetzen.
Ja, Suche mit % am Ende, damit auch Einträge Auflistet werden welche eine abweichenden Durchwahl haben.
Hatte gehofft mySQL hat für so etwas eine elegante Funktion an Board.
Hallo LastBoyScout,
ja, hat es. LIKE.
Wenn die Spalten indexiert sind, ist ein LIKE mit % am Ende effizient. Die Wahrheit liefert aber immer nur der Explain.
Ich meine jedoch, dass die Zeiten längst vorbei sind, wo man „aus Performancegründen“ die Abfrage PHONE LIKE '$search' durch PHONE >= '$search' AND PHONE < '{$search}999999999' ersetzt hat.
Rolf
ja, hat es. LIKE.
Meine eigentlich eine Funktion welche bei einer Suche auch die Bezeichnung Treffer liefernder Spalte/n zurück gibt.
Hi Andreas,
Wieso 'jener Spalte'? 'jene Spalten' wäre doch richtiger.
Ich kenne Leute, die haben gar keine Festnetznummer, die geben bei solchen Formularen bei PHONE und bei MOBILE ihre einzige Nummer an …
Ne, Singular ist schon korrekt...
Handelt sich hierbei um eine einigermaßen gepflegte lokale Datenbank 😉
Hello,
Ich Suche in einer mySQL- Tabelle über mehre Spalten hinweg:
SELECT * FROM tabelle WHERE PHONE LIKE '$search' OR MOBILE LIKE '$search' OR FAX LIKE '$search'Nun würde ich bei einem Treffer gerne auch die Bezeichnung jener Spalte erhalten, welche letztendlich den Treffer liefert. Gibt es hierzu eine elegante Möglichkeit? Oder muss ich hierzu das Ergebnis mit anderen Mitteln erneut auswerten?
Habe schon recherchiert, aber hierzu leider keinen Anhaltspunkt gefunden.
Du könntest das Statement mit der if()-Funktion weiter verkomplizieren.
Oder Du könntest weiter normalisieren.
clientno type data
--------------------------------
70678 Fax 0531/12345-7
70678 Tel 0531/12345-0
70678 Mail hans@dampf.lan
70678 Mob 0171/555333555
Glück Auf
Tom vom Berg
Oder Du könntest weiter normalisieren.
clientno type data -------------------------------- 70678 Fax 0531/12345-7 70678 Tel 0531/12345-0 70678 Mail hans@dampf.lan 70678 Mob 0171/555333555
Könntest Du das Bitte perzisieren, stehe da momentan auf dem Schlauch!?
Hello,
Oder Du könntest weiter normalisieren.
clientno type data -------------------------------- 70678 Fax 0531/12345-7 70678 Tel 0531/12345-0 70678 Mail hans@dampf.lan 70678 Mob 0171/555333555Könntest Du das Bitte perzisieren, stehe da momentan auf dem Schlauch!?
Statt dass Du dir einen Krampf in der SQL-Schicht codest mit dem vorhandenen (schlechten) Datenmodell und dann voraussichtlich auch in der API noch so verknotet weitermachen musst, könntest Du dein Datenmodell überdenken, und die Eigenschaften (Tel, Fax, Mobile, Mail, ...) aus der Waagerechten (dem Datensatz) herausnehmen und in die Senkrechte (eine eigene Tabelle) verfrachten.
Und plötzlich wären die Zuordnungsprobleme verschwunden.
Glück Auf
Tom vom Berg
n'Abend,
clientno type data -------------------------------- 70678 Fax 0531/12345-7 70678 Tel 0531/12345-0 70678 Mail hans@dampf.lan 70678 Mob 0171/555333555Könntest Du das Bitte perzisieren, stehe da momentan auf dem Schlauch!?
Statt dass Du dir einen Krampf in der SQL-Schicht codest mit dem vorhandenen (schlechten) Datenmodell und dann voraussichtlich auch in der API noch so verknotet weitermachen musst, könntest Du dein Datenmodell überdenken, und die Eigenschaften (Tel, Fax, Mobile, Mail, ...) aus der Waagerechten (dem Datensatz) herausnehmen und in die Senkrechte (eine eigene Tabelle) verfrachten.
für diese Anregung hast du von mir schon ein Plus bekommen, zumal dein Beispiel sehr schön zeigt, dass man dann mühelos noch weitere Kontaktmöglichkeiten einbringen kann (hier die Mailadresse).
Allerdings würde ich dann dringend raten, die Telefonnummern auch normalisiert zu speichern. Dein Beispielauszug enthält noch verschiedene Trennzeichen. Die würde ich dann komplett rauslöschen.
Und plötzlich wären die Zuordnungsprobleme verschwunden.
Genau. Wald? Was für'n Wald? Hier sind nur lauter Bäume.
Live long and pros healthy,
Martin
Liebe Mitdenker, liebe Wissende, liebe Neugierige,
n'Abend,
clientno type data -------------------------------- 70678 Fax 0531/12345-7 70678 Tel 0531/12345-0 70678 Mail hans@dampf.lan 70678 Mob 0171/555333555Könntest Du das Bitte perzisieren, stehe da momentan auf dem Schlauch!?
Statt dass Du dir einen Krampf in der SQL-Schicht codest mit dem vorhandenen (schlechten) Datenmodell und dann voraussichtlich auch in der API noch so verknotet weitermachen musst, könntest Du dein Datenmodell überdenken, und die Eigenschaften (Tel, Fax, Mobile, Mail, ...) aus der Waagerechten (dem Datensatz) herausnehmen und in die Senkrechte (eine eigene Tabelle) verfrachten.
für diese Anregung hast du von mir schon ein Plus bekommen, zumal dein Beispiel sehr schön zeigt, dass man dann mühelos noch weitere Kontaktmöglichkeiten einbringen kann (hier die Mailadresse).
Allerdings würde ich dann dringend raten, die Telefonnummern auch normalisiert zu speichern. Dein Beispielauszug enthält noch verschiedene Trennzeichen. Die würde ich dann komplett rauslöschen.
Da Speicherplatz nicht mehr sooo teuer ist, baue ich in solchen Fällen immer zwei Datenspalten auf. Eine mit Formatierung (Slashes, Dashes, Umlaute, Nicht-ASCII-Zeichen, Trallala) und eine normalisierte. Die normalisierte wird immer automatisch durch einen Trigger und eine Benutzerfunktion beschrieben, wenn sich in der formatierten etwas ändert. Die normalisierte ist gegen direktes Beschreiben geschützt.
Spirituelle Grüße
Euer Robert
Da Speicherplatz nicht mehr sooo teuer ist, baue ich in solchen Fällen immer zwei Datenspalten auf. Eine mit Formatierung (Slashes, Dashes, Umlaute, Nicht-ASCII-Zeichen, Trallala) und eine normalisierte.
Das ist natürlich der eleganteste Weg, um derartige Unzulänglichkeiten von vornherein zu vermeiden. 👍 Wünschte die Urheber der DB würden das mal beherzigen.🤬
Hallo LastBoyScout,
hast Du keine Leserechte auf der DB, oder sollst Du sie einfach nur lesen?
Es gibt zwei Möglichkeiten, sich vom DBMS helfen zu lassen, ohne dass die Originalanwendung etwas davon mitbekommt (bis auf minimale Tempoverluste):
(1) Ein Update-Trigger, der Änderungen an Fon, Mob und Fax automatisch von Sonderzeichen befreit und den Wert passend in eine andere Tabelle einträgt. Für diese bietet sich dann die bereits vorgeschlagene Struktur an, wo ein Satz immer nur eine Nummer enthält. Der Trigger ist nicht ganz trivial und braucht etwas Logik, er muss ja auch den Satz in der Suchtabelle löschen können, wenn ein Feld geleert wird. Bei Neueintragungen und Änderungen gibt's INSERT mit ON DUPLICATE KEY UPDATE.
(2) Ist Dir das zu kompliziert, kann man auch per ALTER TABLE drei materialisierte, berechnete Spalten für Fon, Mob und Fax in die Originaltabelle einsetzen. Die Berechnungsformel kann die REPLACE-Ausdrücke enthalten.
Rolf
Hello,
Da Seicherplatz nicht mehr sooo teuer ist, baue ich in solchen Fällen immer zwei Datenspalten auf. Eine mit Formatierung (Slashes, Dashes, Umlaute, Nicht-ASCII-Zeichen, Trallala) und eine normalisierte. Die normalisierte wird immer automatisch durch einen Trigger und eine Benutzerfunktion beschrieben, wenn sich in der formatierten etwas ändert. Die normalisierte ist gegen direktes Beschreiben geschützt.
Genaugenommen muss man Aktionen zu dieser Spalte in zwei Triggern vorsehen:
DELETE kann man sich wohl schenken, wenn die Spalte in derselben Tabelle steht. Wenn man die Information allerdings in eine andere Tabelle auslagert, benötigt man auch einen DELETE-Trigger.
Glück Auf
Tom vom Berg
Lieber Tom,
Da Seicherplatz nicht mehr sooo teuer ist, baue ich in solchen Fällen immer zwei Datenspalten auf. Eine mit Formatierung (Slashes, Dashes, Umlaute, Nicht-ASCII-Zeichen, Trallala) und eine normalisierte. Die normalisierte wird immer automatisch durch einen Trigger und eine Benutzerfunktion beschrieben, wenn sich in der formatierten etwas ändert. Die normalisierte ist gegen direktes Beschreiben geschützt.
Genaugenommen muss man Aktionen zu dieser Spalte in zwei Triggern vorsehen:
- INSERT
- UPDATE
Das stimmt wohl. Aber man kann sollte dieselbe benutzerdefinierte Funktion verwenden, die in den beiden Triggern eingebunden wird. Sonst könnte bei Änderungen schnell mal etwas in die Hose gehen.
DELETE kann man sich wohl schenken, wenn die Spalte in derselben Tabelle steht. Wenn man die Information allerdings in eine andere Tabelle auslagert, benötigt man auch einen DELETE-Trigger.
Der Punkt geht jedenfalls an Dich ;-)
Spirituelle Grüße
Dein Robert
Hi Tom,
aus der Waagerechten (dem Datensatz) herausnehmen und in die Senkrechte (eine eigene Tabelle) verfrachten.
Jetzt hab ich es kapiert... wirklich sehr einleuchtend erklärt DANKE!
Nur leider bin ich bez. des Datenbankmodell an die Hauptanwendung gebunden und kann es daher nicht selbst ändern. Somit muss ich mit den Unzulänglichkeiten mittels eines geeigneten Workaround irgendwie zurechtkommen. Bin ja zumindest schon mal froh, das wenigstens keine Duplikate vorhanden sind.
Hello,
aus der Waagerechten (dem Datensatz) herausnehmen und in die Senkrechte (eine eigene Tabelle) verfrachten. Jetzt hab ich es kapiert... wirklich sehr einleuchtend erklärt DANKE!
Nur leider bin ich bez. des Datenbankmodell an die Hauptanwendung gebunden und kann es daher nicht selbst ändern. Somit muss ich mit den Unzulänglichkeiten mittels eines geeigneten Workaround irgendwie zurechtkommen. Bin ja zumindest schon mal froh, das wenigstens keine Duplikate vorhanden sind.
Vielleicht kannst Du eine Hilfstabelle aufbauen?
Um wieviele Datensätze handelt es sich insgesamt?
Wie oft ändert sich der Datenbestand?
Glück Auf
Tom vom Berg
Vielleicht kannst Du eine Hilfstabelle aufbauen?
Da ich keine internen Kenntnisse um die Hauptanwendung habe, möchte ich lieber nicht Hand an die Datenbankarchitektur legen. Müsste das dann eher in eine zweite DB auslagern... Nehme dann aber doch lieber einen entsprechenden Workaround in kauf als eine zweite DB zu pflegen.
Um wieviele Datensätze handelt es sich insgesamt?
Sind so um die 500 Datensätze... Performance spielt daher eher eine untergeordnete rolle.
Wie oft ändert sich der Datenbestand?
Änderungen sind eigentlich selten. Eher kommen hin und wieder neue Daten hinzu.
Hallo LastBoyScout,
ich hätte zwei Alternativen.
(1) Verwende UNION ALL statt OR:
SELECT tabelle.*, 'PHONE' as fundstelle
FROM tabelle
WHERE PHONE LIKE '$search'
UNION ALL
SELECT tabelle.*, 'MOBILE' as fundstelle
FROM tabelle
WHERE MOBILE LIKE '$search'
UNION ALL
SELECT tabelle.*, 'FAX' as fundstelle
FROM tabelle
WHERE FAX LIKE '$search'
(2) Ermittle die Fundstelle über einen CASE-Ausdruck.
SELECT tabelle.*,
CASE WHEN PHONE LIKE '$search' THEN 'PHONE'
WHEN MOBILE LIKE '$search' THEN 'MOBILE'
WHEN FAX LIKE '$search' THEN 'FAX' END as fundStelle
FROM tabelle
WHERE PHONE LIKE '$search' OR MOBILE LIKE '$search' OR FAX LIKE '$search'
Schön ist beides nicht. In der ersten Variante rennst Du 3x durch die Tabelle, in der zweiten Variante prüfst Du alles doppelt. Vermutlich ist die 2. Variante schneller.
Ich überlege gerade noch ob man eine Cursorschleife sinnvoll einsetzen kann, aber das dürfte unter dem Strich am mühsamsten und langsamsten sein.
Dass Du beim befüllen der Variablen $search an's Escapen denken musst, schreibe ich mal rein pro forma unten drunter 😉
Rolf
Hallo Rolf,
Danke für deine Anregungen... tendiere wohl zu Lösung 2
Etwas detaillierter sieht es bei mir aktuell so aus, wobei ich beim Suchstring nur Zahlen durchlasse:
SELECT * FROM tabelle
WHERE REPLACE(REPLACE(REPLACE(PHONE, ' ', ''), '/', ''), '-', '') LIKE '$search%'
OR REPLACE(REPLACE(REPLACE(MOBILE, ' ', ''), '/', ''), '-', '') LIKE '$search%'
OR REPLACE(REPLACE(REPLACE(FAX, ' ', ''), '/', ''), '-', '') LIKE '$search%'
Hallo,
SELECT * FROM tabelle WHERE REPLACE(REPLACE(REPLACE(PHONE, ' ', ''), '/', ''), '-', '') LIKE '$search%' OR REPLACE(REPLACE(REPLACE(MOBILE, ' ', ''), '/', ''), '-', '') LIKE '$search%' OR REPLACE(REPLACE(REPLACE(FAX, ' ', ''), '/', ''), '-', '') LIKE '$search%'
ohne es zu wissen, stelle ich einfach mal zur Diskussion: Ist die String-Manipulation (Blank, Slash und Hyphen entfernen) in der Host-Applikation eventuell effizienter als ein dreifach verschachtelter SQL-Funktionsaufruf?
Live long and pros healthy,
Martin
Tach!
Etwas detaillierter sieht es bei mir aktuell so aus, wobei ich beim Suchstring nur Zahlen durchlasse:
SELECT * FROM tabelle WHERE REPLACE(REPLACE(REPLACE(PHONE, ' ', ''), '/', ''), '-', '') LIKE '$search%' OR REPLACE(REPLACE(REPLACE(MOBILE, ' ', ''), '/', ''), '-', '') LIKE '$search%' OR REPLACE(REPLACE(REPLACE(FAX, ' ', ''), '/', ''), '-', '') LIKE '$search%'
Solche Suchanfragen sollte man nicht auf ein DBMS loslassen. Felder, die erst noch berechnet werden müssen, bekommt man nur mit einem Full Table Scan gefunden. Speicher lieber ein extra Feld mit der selben Nummer ohne Sonderzeichen, dann kann das DBMS auch einen Index verwenden.
dedlfix.
Hallo LastBoyScout,
aua, das sieht nicht hübsch aus. Dieses Klammerdickicht muss dann ja auch noch in den CASE hinein.
Der UNION ALL ist dann wohl lesbarer.
Ich habe gerade auch noch über eine UNPIVOTisierung nachgedacht, d.h. du erzeugst Dir eine temporäre Tabelle in dem Stil wie Tom sie vorgeschlagen hat. Das geht ebenfalls über UNION ALL. Problem ist nur, dass das bei einer großen Tabelle viel Zeit kostet.
Wie groß ist deine Tabelle? Wie oft suchst Du darin? Kann es sinnvoll sein, berechnete Spalten hinzuzufügen, in der die Telefonnummern ohne Sonderzeichen stehen? Kann es sinnvoll sein, eine Zusatztabelle einzuführen, wo die gefilterten Werte drin stehen und wo jede Nummer zusammen mit Verwendungszweck in einer einzelnen Zeile steht? So eine Suchtabelle muss man pflegen, ja, aber falls die Suche andernfalls grottenlahm wird...
Rolf
Mir ist bewusst das diese Verschachtelung äußerst unschön ist... leider kann ich hier auf die Tabelle nur lesend zugreifen, da es sich um Daten einer anderen Anwendung handelt. Und da nicht immer klar ist, wie die Rufnummer eingetragen wurde, muss ich das ja irgendwie berücksichtigen!?
P.S. Hintergrund ist, bei einem eingehenden Anruf den passenden Datensatz zu ermitteln.