mySQL: Substring finden

[Linuchs]

Moin,

Listen kann ich mit LIKE nach Zeichenfolgen filtern, um die gefundenen Zeichenfolgen im String hervorzuheben:

SELECT
 *
,INSTR(titel, 'pa')  startposi
FROM      shanty_titel
WHERE     titel   LIKE '%pa%'
ORDER BY  titel, sprache

SQL findet auch ähnliche Zeichenfolgen, z.B. på statt pa, deren Position ich in der Variablen startposi gerne hätte, aber dort wird nur dieselbe Schreibweise, also pa aber nicht på gefunden.

Wie kann ich dieses Problem lösen?

Gruß, Linuchs

[Rolf B]

Hallo Linuchs,

willkommen in der Welt der Zeichencodierungen und Collations.

Welche Collation hat die Spalte, in der Du suchst? Abhängig davon erfolgt die Suche accent sensitive oder nicht.

Eventuell kannst Du mit der COLLATE-Klausel eine ci-Collation für die Suche angeben, aber nicht alle DB-Collations sind kompatibel. Eine ci-Collation ist eine, deren Name auf _ci endet. Das MySQL Handbuch listet keine _as oder _ai Collations (accent sensitive/insensitive), aber vielleicht gibt's sie; das weiß ich gerade nicht. Mit dem Befehl SHOW CHARACTER SET listest du die vorhandenen Collations auf.

Leichte Bettlektüre

Rolf

[Der Martin]

Hallo,

willkommen in der Welt der Zeichencodierungen und Collations.

die ist immer einen sorgfältigen Blick wert. Aber ...

Welche Collation hat die Spalte, in der Du suchst? Abhängig davon erfolgt die Suche accent sensitive oder nicht.

Ich vermute, du hast Linuchs falsch verstanden. Oder ich. ;-)

Eventuell kannst Du mit der COLLATE-Klausel eine ci-Collation für die Suche angeben, aber nicht alle DB-Collations sind kompatibel. Eine ci-Collation ist eine, deren Name auf _ci endet. Das MySQL Handbuch listet keine _as oder _ai Collations (accent sensitive/insensitive), aber vielleicht gibt's sie; das weiß ich gerade nicht. Mit dem Befehl SHOW CHARACTER SET listest du die vorhandenen Collations auf.

Ich verstehe ihn eher so, dass es gewollt ist, dass das SELECT-Statement auch Stellen mit diakritischen Zeichen findet - die INSTR()-Funktion will's aber genau wissen und liefert für ein gefundenes på keinen Treffer, wenn pa gesucht ist.

Ciao,
 Martin

[Linuchs]

Ich verstehe ihn eher so, dass es gewollt ist, dass das SELECT-Statement auch Stellen mit diakritischen Zeichen findet - die INSTR()-Funktion will's aber genau wissen und liefert für ein gefundenes på keinen Treffer, wenn pa gesucht ist.

Ja, die Zeichen mit Akzent sollen gefunden werden.

[MudGuard]

Hi,

SELECT
 *
,INSTR(titel, 'pa')  startposi
FROM      shanty_titel
WHERE     titel   LIKE '%pa%'
ORDER BY  titel, sprache

SQL findet auch ähnliche Zeichenfolgen, z.B. på statt pa, deren Position ich in der Variablen startposi gerne hätte, aber dort wird nur dieselbe Schreibweise, also pa aber nicht på gefunden.

Nur um zu klären, was Du meinst:

LIKE ist tolerant und akzeptiert auch akzentierte a, während INSTR nur das a selbst findet.

Hm. Klingt nach einem Collation-Problem. Wüßte aber auch nicht, wie das zu lösen wäre - es fehlt mysql wohl sowas wie ein regexp_locate oder regexp_instr …

Wie kann ich dieses Problem lösen?

Gruß, Linuchs

cu,
Andreas a/k/a MudGuard

[MudGuard]

Hi,

Hm. Klingt nach einem Collation-Problem. Wüßte aber auch nicht, wie das zu lösen wäre - es fehlt mysql wohl sowas wie ein regexp_locate oder regexp_instr …

fehlt nicht - zumindest in Version 8 ist's vorhanden:

regexp_instr

cu,
Andreas a/k/a MudGuard

[Linuchs]

SELECT
 *
,INSTR(titel, 'pa')  startposi
FROM      shanty_titel
WHERE     titel   LIKE '%pa%'
ORDER BY  titel, sprache

Hm. Klingt nach einem Collation-Problem.

Kollation des Feldes titel ist utf8_unicode_ci

[Rolf B]

Hallo Linuchs,

jetzt, wo ich es auch verstanden habe, habe ich mal ausprobiert.

Man kann zwar der INSTR Funktion eine COLLATE Option hinzufügen, es scheint aber, als würde sie das nicht beachten.

Die _ci Collations sollen angeblich auch accent-insensitive sein, aber die String-Funktionen beachten das möglicherweise nicht.

Ich habe gerade mal ausprobiert:

SELECT *, INSTR(titel COLLATE utf8_unicode_ci, 'pa') startposi FROM ...
SELECT *, INSTR(titel, 'pa' COLLATE utf8_unicode_ci) startposi FROM ...
SELECT *, INSTR(titel COLLATE utf8_unicode_ci , 'pa' COLLATE utf8_unicode_ci ) startposi FROM ...

COLLATE hinter dem INSTR ist Quatsch, INSTR liefert eine Zahl und darauf ist collate nicht sinnvoll anwendbar :)

Jedenfalls hat das alles nichts geändert, INSTR scheint - auf meinem MYSQL jedenfalls - zu doof. _ai Collations kommen wohl erst mit MySQL 8 (wenn überhaupt)

Rolf

[Linuchs]

Hallo Rolf,

danke fürs Probieren.

Ich lasse es mal dabei, dass die zusätzlich wg. Akzent gefundenen Lied-Titel zwar angezeigt, aber der Suchbegriff nicht markiert wird.

Gruß, Linuchs