MySQL Abfrage von Werten mit "2 gleichen Buchstaben" im Wort

marspoki · 9 Mai 2024

Hallo Ihr Profis,

ich habe mal eine Frage.

Ist es möglich, eine MySQL suche so zu schreiben das ich Wörter finde in denen z.B. 2x der Buchstabe a enhalten ist ?

SELECT * FROM staedte WHERE stadt REGEXP 'a{2}' <-findet ja z.B. doppel aa / das will ich aber nicht

SELECT * FROM staedte WHERE stadt LIKE '%a%' AND stadt Like '%a%' <- ist ja auch quatsch weil er ja auch Mannheim anzeigen tut.

Ich möchte gern z.B. Ballhausen finden und nicht z.B. Mannheim.

Ist so etwas möglich?

Vielen Dank und Viele Grüße
Klaus

akretschmer · 9 Mai 2024

mit passender regex sicherlich ja.

castorp · 9 Mai 2024

Diese Regex sollte funktionieren '[^a]*[a]+[^a]*[a]+'

BerndB · 9 Mai 2024

Hallo Klaus,

ich würde da REGEXP nehmen. Damit kannst du die Eigenschaften "einfach" angeben.
Was mir noch nicht klar ist, ob du auch Worte mit "aaxxxxxxa" finden möchtest.
Hier einmal einige Beispiele:

Code:

SELECT * FROM staedte WHERE stadt REGEXP 'a[^a]*a';

Code:

mysql> SELECT stadt, stadt REGEXP 'a[^a]*a' FROM staedte;


+----------+------------------------+
| stadt    | stadt REGEXP 'a[^a]*a' |
+----------+------------------------+
| abc      |                      0 |
| abca     |                      1 |
| aabbcc   |                      1 |
| aabbcca  |                      1 |
| aabbccaa |                      1 |
+----------+------------------------+
5 rows in set (0.00 sec)

mysql>

Hier bedeutet der reguläre Ausdruck:
a: Übereinstimmung mit dem Buchstaben 'a'.
[^a]*: Übereinstimmung mit null oder mehr Vorkommen von beliebigen Zeichen außer 'a'.
a: Übereinstimmung mit dem Buchstaben 'a'.

Wenn du nun noch ausschließen möchtest das überhaupt einmal "aa vorkommt kannst du folgendes angeben

Code:

SELECT * FROM staedte WHERE stadt REGEXP '^(?!.*aa).*a[^a]*a.*$';

Code:

mysql> SELECT stadt, stadt REGEXP '^(?!.*aa).*a[^a]*a.*$'  FROM staedte;

+----------+--------------------------------------+
| stadt    | stadt REGEXP '^(?!.*aa).*a[^a]*a.*$' |
+----------+--------------------------------------+
| abc      |                                    0 |
| abca     |                                    1 |
| aabbcc   |                                    0 |
| aabbcca  |                                    0 |
| aabbccaa |                                    0 |
+----------+--------------------------------------+
5 rows in set (0.00 sec)

mysql>

Die Bedeutung dazu ist:

^: Start des Strings.
(?!.*aa): Negative lookahead Assertion, die sicherstellt, dass "aa" im gesamten String nicht vorkommt.
.*: Beliebige Anzahl von Zeichen.
a: Ein 'a'.
[^a]*: Null oder mehr beliebige Zeichen außer 'a'.
a: Ein weiteres 'a'.
.*: Beliebige Anzahl von Zeichen.
$: Ende des Strings.

tk1234 · 9 Mai 2024

Crossposting: MySQL Abfrage von Werten mit "2 gleichen Buchstaben" im Wort - php.de

marspoki · 10 Mai 2024

@BerndB

vielen Dank das funktioniert echt gut, vielen Dank

Viele Grüße und Danke
Klaus

ukulele · 10 Mai 2024

spalte LIKE '%a%a%' müsste auch funktionieren, wenn es kein direkt folgendes a ist.

marspoki · 3 Juni 2024

Hallo liebe Leute,

eure Abfragen funktionieren einwandfrei, nur leider ist es nicht das was ich eigentlich brauche (habe ich jetzt gemerkt)

SELECT * FROM staedte WHERE stadt REGEXP 'a[^a]*a'; <-- klappt zum beispiel perfekt.

Folgendes Scenario möchte in einem kleinen Quizspiel für unsere Schule programmieren:

In einer Tabelle mit den Städten (staedte) soll mit Buchstabenkombinationen gesucht werden.

Ich möchte z.B. mit folgenden Buchstaben suchen: A S A Z C E N H J E

finden soll er z.B.:

Aachen
Jena
Sahne <- nehmen wir mal an das währe eine Stadt

nicht finden soll er aber

Aachennau (da ja nur 2 mal "A" in den Buchstaben zu finden sind)

Ich hoffe ich habe das verständlich geschrieben.

Vielen Dank schonmal und viele Grüße
Schönen Abend
Klaus

dabadepdu · 4 Juni 2024

Ich bin mir nicht sicher, ob das ein geeigneter Anwendungsfall für Regex ist.
- Du benutzt offenbar keine Wildcards
- Die Reihenfolge ist beliebig
- die Länge der Regex auch, nicht praktisch, aber theoretisch

Du müsstest also aus einer zufälligen Buchstabenmenge jedes Regex generieren.

Damit wärst Du eher bei einer netten algorithmischen Aufgabe. Entweder, um die Regex zu generieren oder gleich, um das Matching zu machen.

Pädagogisch wertvoll fände ich auch eher den Aufbau eines Algorithmus. Den könnte man entweder in der Programmiersprache des Clients machen oder, wenn das gar nicht vorkommt in einer Stored Procedure. Dabei könnte man tatsächlich Mengenbetrachtungen machen, Laufzeitverhalten Client seitige Verarbeitung versus Server. Verhalten bei allen Städten in den Bundesländern oder allen Städten der Welt, Datenmengen, Aufwandsbetrachtung, ...

P.S.: Ich bin mal davon ausgegangen, dass es im Schwerpunkt um Informatik geht, nicht um Geographie.

marspoki · 4 Juni 2024

dabadepdu schrieb:
Ich bin mir nicht sicher, ob das ein geeigneter Anwendungsfall für Regex ist.
- Du benutzt offenbar keine Wildcards
- Die Reihenfolge ist beliebig
- die Länge der Regex auch, nicht praktisch, aber theoretisch

Du müsstest also aus einer zufälligen Buchstabenmenge jedes Regex generieren.

Damit wärst Du eher bei einer netten algorithmischen Aufgabe. Entweder, um die Regex zu generieren oder gleich, um das Matching zu machen.

Pädagogisch wertvoll fände ich auch eher den Aufbau eines Algorithmus. Den könnte man entweder in der Programmiersprache des Clients machen oder, wenn das gar nicht vorkommt in einer Stored Procedure. Dabei könnte man tatsächlich Mengenbetrachtungen machen, Laufzeitverhalten Client seitige Verarbeitung versus Server. Verhalten bei allen Städten in den Bundesländern oder allen Städten der Welt, Datenmengen, Aufwandsbetrachtung, ...

P.S.: Ich bin mal davon ausgegangen, dass es im Schwerpunkt um Informatik geht, nicht um Geographie.

Naja es geht schon um beides - da hast du Recht.

Ich habe schon überlegt ob ich die Städte in der Datenbank aufdrösel und die jeweilige Anzahl der Buchstaben mit in die Datenbank schreibe.

Bsp:
Aachen A 2 B 0 C 1 D 0 usw

Also A B C D E F sind eigene Spalten die die jeweiligen Anzahl der Buchstaben darstellt.

Allerdings komme ich mit der Abfrage dann auch nicht wirklich weiter da ja z.B. eine Abfrage mit A S A Z C A E N H J E (A 3mal enthalten) mir leider nicht mehr die mögliche Stadt "Aachen" ausspuckt.

Schwirig schwirig
Der Server ansich läuft mit php - vielleicht hat jemand eine Idee wie die Abfrage laufen kann?

Vielen Dank und viele Grüße
Klaus

BerndB · 4 Juni 2024

Moin,

ich hab das mal was gemacht was deine Dingen relativ nahe kommt. Du Kannst die SELECTs noch
im WHERE verbessern falls noch Worte ausgeschlossen werden müssen.

Tabelle mit Orten erstellen

Code:

CREATE TABLE words (
    id INT AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(255) NOT NULL
);


INSERT INTO words (word) VALUES
('Aachen'), ('Jena'), ('Sahne'),
('Berlin'),  ('Hamburg'), ('München'), ('Köln'),
('Frankfurt'), ('Stuttgart'), ('Düsseldorf'), ('Dortmund'),
('Essen'), ('Leipzig'), ('Bremen'), ('Dresden'),
('Hannover'), ('Nürnberg'), ('Duisburg'), ('Bochum'),
('Wuppertal'), ('Bielefeld'), ('Bonn'), ('Münster');

Stored Procedure erstellen

Code:

DELIMITER //


CREATE FUNCTION can_be_formed_from(word VARCHAR(255), letters VARCHAR(255))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE c CHAR(1);
    DECLARE pos INT;
    WHILE i <= CHAR_LENGTH(word) DO
        SET c = SUBSTRING(word, i, 1);
        SET pos = LOCATE(c, letters);
        IF pos = 0 THEN
            RETURN FALSE;
        END IF;
        SET letters = INSERT(letters, pos, 1, '');
        SET i = i + 1;
    END WHILE;
    RETURN TRUE;
END;
//


DELIMITER ;

Abfragen

Code:

SELECT word FROM words WHERE can_be_formed_from(word, 'A S A Z C E N H J E');
SELECT word FROM words WHERE can_be_formed_from(word, 'bmrenil');

Beispiel

Code:

mysql> SELECT word FROM words WHERE can_be_formed_from(word, 'A S A Z C E N H J E');
+--------+
| word   |
+--------+
| Aachen |
| Jena   |
| Sahne  |
+--------+
3 rows in set (0.01 sec)


mysql> SELECT word FROM words WHERE can_be_formed_from(word, 'bmrenil');
+--------+
| word   |
+--------+
| Berlin |
+--------+
1 row in set (0.00 sec)

mysql>

Ich hoffe es hilft dir weiter.

Gruß

Bernd

marspoki · 4 Juni 2024

Auf die Schnelle habe ich das mal schnell in meine MySQL gebaut und es funktioniert wie es aussieht genau wie ich es brauche. Ich muss mir das nur noch genau anschauen um zu versteh und zu lernen

DELIMITER lese ich zum erstene mal.

Vielen Dank für deine Antwort.

ukulele · 4 Juni 2024

Also die Reihenfolge der gegebenen Buchstaben ist unerheblich, korrekt? Es geht nur um vorhandene Buchstaben.

Ich würde mit CTE beide Strings in einzelne Buchstaben zerlegen und die Mengen vergleichen. Das geht zumindest mit kleinen Datenmengen ganz gut.

Code:

CREATE TABLE #words (
    id INT PRIMARY KEY,
    word VARCHAR(255) NOT NULL
);

INSERT INTO #words (id,word) VALUES
(1,'Aachen'), (2,'Jena'), (3,'Sahne'),
(4,'Berlin'),  (5,'Hamburg'), (6,'München'), (7,'Köln'),
(8,'Frankfurt'), (9,'Stuttgart'), (10,'Düsseldorf'), (11,'Dortmund'),
(12,'Essen'), (13,'Leipzig'), (14,'Bremen'), (15,'Dresden'),
(16,'Hannover'), (17,'Nürnberg'), (18,'Duisburg'), (19,'Bochum'),
(20,'Wuppertal'), (21,'Bielefeld'), (22,'Bonn'), (23,'Münster');

WITH t AS (
SELECT    0 AS id,
        'ASAZCENHJE' AS wort
UNION ALL
SELECT    id,
        word
FROM    #words
    ), cte AS(
SELECT    id,
        wort,
        upper(left(wort,1)) AS buchstabe,
        1 AS buchstabe_nr,
        right(wort,len(wort)-1) AS wort_rest
FROM    t
UNION ALL
SELECT    id,
        wort,
        upper(left(wort_rest,1)),
        buchstabe_nr + 1,
        right(wort_rest,len(wort_rest)-1)
FROM    cte
WHERE    wort_rest != ''
    ), buchstabensalat AS (
SELECT    id,
        wort,
        buchstabe,
        count(*) AS buchstabe_anzahl
FROM    cte
GROUP BY id,wort,buchstabe
    ), treffer AS (
SELECT    s0.wort AS suche,
        s1.wort,
        (    CASE
            WHEN    sum(s1.buchstabe_anzahl) < sum(s0.buchstabe_anzahl)
            THEN    sum(s1.buchstabe_anzahl)
            ELSE    sum(s0.buchstabe_anzahl)
            END ) AS buchstabe_uebereinstimmung
FROM    buchstabensalat s0
LEFT JOIN buchstabensalat s1
ON        s0.buchstabe = s1.buchstabe
AND        s1.id != 0
WHERE    s0.id = 0
GROUP BY s0.wort,s1.wort
    )
SELECT    DENSE_RANK() OVER (ORDER BY buchstabe_uebereinstimmung DESC) AS rang,
        *
FROM    treffer;

DROP TABLE #words;

Habe das jetzt mit MSSQL geschrieben, habe aber glaube ich nichts verwendet was MySQL nicht auch kann, zumindest in einer neueren Version. Syntax sollte auch passen, sry für den Denglisch-Mischmasch, habe die Testtabelle von @BerndB .

PS: Natürlich wäre es sinnvoll, die Buchstabenmengen der Orte bereits irgendwo abzuspeichern und nicht jedes Mal on the fly neu zu ermitteln. Dann muss man das nur noch für den Suchstring machen, da kann eigentlich nicht viel schief gehen.

dabadepdu · 5 Juni 2024

Idee von @ukulele ist schön!
Beide Lösungen von @BerndB und @ukulele gefallen mir.

Welche man nimmt, kommt darauf an, was man vermitteln möchte.
Algorithmen, SQL oder Geographie?
Vielleicht ist es ja Crossover Unterricht.

Die SQL Lösung ist schön, weil sie simpel ist, reines SQL und SQL Prinzipien vermittelt und das Verfahren der Daten- Normalisierung zwecks Matching einsetzt (nicht verwechseln mit Datenmodell Normalisierung). In der "allgemeingültigen" Formulierung von ukulele ist sie leider sehr lang und nicht sehr übersichtlich.

In postgres habe ich diese Lösung gefunden, geht vielleicht auch in mySQL und ist viel übersichtlicher und damit verständlicher. Basiert auf der word Tabelle von @BerndB:

Code:

select y.* from (
  select id, array_agg(letterp) ar from (
    select w.*, p.* from 
    (select id, regexp_split_to_table (lower(value),'') as letterw from word) w 
    left join 
    (select regexp_split_to_table ('bmrenil','') as letterp) p
    on w.letterw = p.letterp) x
  group by id) y
where array_position(ar,null) is null;

Bestimmt kann man noch geschickter die NULL Werte bzw. Städte filtern und spart sich noch das Group By usw. ...

Postgres 16

| version | |:--------| | PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86\_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit | > ``` status > SELECT 1 > ```

dbfiddle.uk

ukulele · 6 Juni 2024

Ja die Funktion split to table in PG ist ganz nice, habe ich noch in keinem anderen DBMS so gesehen.

MySQL Abfrage von Werten mit "2 gleichen Buchstaben" im Wort

marspoki

Benutzer

akretschmer

Datenbank-Guru

castorp

Datenbank-Guru

BerndB

Datenbank-Guru

tk1234

Benutzer

marspoki

Benutzer

ukulele

Datenbank-Guru

marspoki

Benutzer

dabadepdu

Datenbank-Guru

marspoki

Benutzer

BerndB

Datenbank-Guru

marspoki

Benutzer

ukulele

Datenbank-Guru

dabadepdu

Datenbank-Guru

Postgres 16

ukulele

Datenbank-Guru

Ähnliche Themen