Wie extrahiere ich zwei aufeinanderfolgende Ziffern aus einem Textfeld in MySQL?

Ich habe eine MySQL-database und ich habe eine Abfrage wie folgt:

SELECT `id`, `originaltext` FROM `source` WHERE `originaltext` regexp '[0-9][0-9]' 

Damit werden alle Originaltexte erkannt, die Ziffern mit 2 Ziffern enthalten.

Ich brauche MySQL, um diese Zahlen als ein Feld zurückzugeben , also kann ich sie weiter manipulieren.

Im Idealfall, wenn ich zusätzliche Kriterien hinzufügen kann, die> 20 sein sollten, wäre großartig, aber ich kann das auch separat tun.

Wenn Sie mehr reguläre Ausdruckskraft in Ihrer database wünschen, können Sie LIB_MYSQLUDF_PREG verwenden . Dies ist eine Open-Source-Bibliothek von MySQL-Benutzerfunktionen, die die PCRE-Bibliothek importiert. LIB_MYSQLUDF_PREG wird nur im Quellcodeformat ausgeliefert. Um es verwenden zu können, müssen Sie in der Lage sein, es zu kompilieren und es auf Ihrem MySQL-Server zu installieren. Die Installation dieser Bibliothek ändert die in MySQL integrierte Regex-Unterstützung in keiner Weise. Es stellt lediglich die folgenden zusätzlichen functionen zur Verfügung:

PREG_CAPTURE extrahiert eine Regex-Übereinstimmung aus einer Zeichenfolge. PREG_POSITION gibt die Position zurück, an der ein regulärer Ausdruck mit einer Zeichenfolge übereinstimmt. PREG_REPLACE führt ein Suchen und Ersetzen in einer Zeichenfolge durch. PREG_RLIKE testet, ob eine Regex mit einer Zeichenfolge übereinstimmt.

Alle diese functionen nehmen einen regulären Ausdruck als ihren ersten Parameter an. Dieser reguläre Ausdruck muss wie ein Perl-Operator für reguläre Ausdrücke formatiert sein. Um beispielsweise zu testen, ob Regex den Fall des Subjekts insensitiv behandelt, würden Sie den MySQL-Code PREG_RLIKE (‘/ regex / i’, Betreff) verwenden. Dies ähnelt den Preg-functionen von PHP, die zusätzlich die // Trennzeichen für reguläre Ausdrücke innerhalb der PHP-Zeichenfolge benötigen.

Wenn Sie etwas einfacheres möchten, können Sie diese function an Ihre Bedürfnisse anpassen.

 CREATE FUNCTION REGEXP_EXTRACT(string TEXT, exp TEXT) -- Extract the first longest string that matches the regular expression -- If the string is 'ABCD', check all strings and see what matches: 'ABCD', 'ABC', 'AB', 'A', 'BCD', 'BC', 'B', 'CD', 'C', 'D' -- It's not smart enough to handle things like (A)|(BCD) correctly in that it will return the whole string, not just the matching token. RETURNS TEXT DETERMINISTIC BEGIN DECLARE s INT DEFAULT 1; DECLARE e INT; DECLARE adjustStart TINYINT DEFAULT 1; DECLARE adjustEnd TINYINT DEFAULT 1; -- Because REGEXP matches anywhere in the string, and we only want the part that matches, adjust the expression to add '^' and '$' -- Of course, if those are already there, don't add them, but change the method of extraction accordingly. IF LEFT(exp, 1) = '^' THEN SET adjustStart = 0; ELSE SET exp = CONCAT('^', exp); END IF; IF RIGHT(exp, 1) = '$' THEN SET adjustEnd = 0; ELSE SET exp = CONCAT(exp, '$'); END IF; -- Loop through the string, moving the end pointer back towards the start pointer, then advance the start pointer and repeat -- Bail out of the loops early if the original expression started with '^' or ended with '$', since that means the pointers can't move WHILE (s < = LENGTH(string)) DO SET e = LENGTH(string); WHILE (e >= s) DO IF SUBSTRING(string, s, e) REGEXP exp THEN RETURN SUBSTRING(string, s, e); END IF; IF adjustEnd THEN SET e = e - 1; ELSE SET e = s - 1; -- ugh, such a hack to end it early END IF; END WHILE; IF adjustStart THEN SET s = s + 1; ELSE SET s = LENGTH(string) + 1; -- ugh, such a hack to end it early END IF; END WHILE; RETURN NULL; END 

Es gibt in MySQL keine Syntax zum Extrahieren von Text mit regulären Ausdrücken. Sie können REGEXP verwenden, um die Zeilen zu identifizieren, die zwei aufeinanderfolgende Ziffern enthalten, aber um sie zu extrahieren, müssen Sie die gewöhnlichen functionen zur Zeichenfolgenbearbeitung verwenden, was in diesem Fall sehr schwierig ist.

Alternativen:

  • Wählen Sie den gesamten Wert aus der database und verwenden Sie dann einen regulären Ausdruck auf dem Client.
  • Verwenden Sie eine andere database, die eine bessere Unterstützung für den SQL-Standard bietet (möglicherweise keine Option, ich weiß). Dann kannst du folgendes verwenden: SUBSTRING(originaltext from '%#[0-9]{2}#%' for '#') .

Ich habe das gleiche Problem, und das ist die Lösung, die ich gefunden habe (aber es wird nicht in allen Fällen funktionieren):

  • Verwenden Sie LOCATE() , um den Anfang und das Ende der Zeichenfolge zu finden, mit der Sie übereinstimmen möchten
  • benutze MID() , um den Teilstring dazwischen zu extrahieren …
  • Halte die Regexp so, dass sie nur mit den Zeilen übereinstimmt, in denen du sicher bist, dass sie eine Übereinstimmung gefunden haben.

Ich habe meinen Code als Stored Procedure (Function) verwendet, um eine beliebige Zahl aus Ziffern in einem Block zu extrahieren. Dies ist ein Teil meiner breiteren Bibliothek.

 DELIMITER $$ -- 2013.04 michal@glebowski.pl -- FindNumberInText("ab 234 95 cd", TRUE) => 234 -- FindNumberInText("ab 234 95 cd", FALSE) => 95 DROP FUNCTION IF EXISTS FindNumberInText$$ CREATE FUNCTION FindNumberInText(_input VARCHAR(64), _fromLeft BOOLEAN) RETURNS VARCHAR(32) BEGIN DECLARE _r VARCHAR(32) DEFAULT ''; DECLARE _i INTEGER DEFAULT 1; DECLARE _start INTEGER DEFAULT 0; DECLARE _IsCharNumeric BOOLEAN; IF NOT _fromLeft THEN SET _input = REVERSE(_input); END IF; _loop: REPEAT SET _IsCharNumeric = LOCATE(MID(_input, _i, 1), "0123456789") > 0; IF _IsCharNumeric THEN IF _start = 0 THEN SET _start = _i; END IF; ELSE IF _start > 0 THEN LEAVE _loop; END IF; END IF; SET _i = _i + 1; UNTIL _i > length(_input) END REPEAT; IF _start > 0 THEN SET _r = MID(_input, _start, _i - _start); IF NOT _fromLeft THEN SET _r = REVERSE(_r); END IF; END IF; RETURN _r; END$$ 

Wenn Sie einen Teil eines Strings zurückgeben möchten:

 SELECT id , substring(columnName,(locate('partOfString',columnName)),10) from tableName; 

Locate() gibt die Startposition der übereinstimmenden Zeichenfolge zurück, die zur Startposition von Function Substring()

Ich weiß, dass es schon eine ganze Weile her ist, seit diese Frage gestellt wurde, aber ich fand sie und dachte, es wäre eine gute Herausforderung für meinen Custom Regex Replacer – siehe diesen Blogbeitrag .

… Und die gute Nachricht ist, dass es kann, obwohl es einige Male aufgerufen werden muss. Sehen Sie diese Online-Rextester-Demo , die die Arbeiten zeigt, die zur SQL unten kamen.

 SELECT reg_replace( reg_replace( reg_replace( reg_replace( reg_replace( reg_replace( reg_replace(txt, '[^0-9]+', ',', TRUE, 1, -- Min match length 0 -- No max match length ), '([0-9]{3,}|,[0-9],)', '', TRUE, 1, -- Min match length 0 -- No max match length ), '^[0-9],', '', TRUE, 1, -- Min match length 0 -- No max match length ), ',[0-9]$', '', TRUE, 1, -- Min match length 0 -- No max match length ), ',{2,}', ',', TRUE, 1, -- Min match length 0 -- No max match length ), '^,', '', TRUE, 1, -- Min match length 0 -- No max match length ), ',$', '', TRUE, 1, -- Min match length 0 -- No max match length ) AS `csv` FROM tbl;