How to Extract Consecutive Digits from a MySQL Text Field and Apply Additional Criteria?

Extracting Consecutive Digits from a MySQL Text Field

In MySQL, the following query can detect text fields containing 2-digit numbers:

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

However, to manipulate these numbers as a separate field, further processing is required. Here are two potential solutions:

Using LIB_MYSQLUDF_PREG

LIB_MYSQLUDF_PREG is a MySQL library that enhances regular expression functionality. It introduces functions like PREG_CAPTURE, which can extract matches from a string.

To use this library, you must install it into your MySQL server. Once installed, you can use the following query to extract consecutive digits:

SELECT `id`, PREG_CAPTURE('[0-9][0-9]', `originaltext`) AS `digits` FROM `source`

Using a Custom MySQL Function

Alternatively, you can define a custom MySQL function that performs the extraction. For example, the following function will extract the first longest matching sequence of digits:

CREATE FUNCTION REGEXP_EXTRACT(string TEXT, exp TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
  DECLARE s INT DEFAULT 1;
  ...
  -- Implementation to extract the matching digits
  ...
END

To use this function, call it as follows:

SELECT `id`, REGEXP_EXTRACT(`originaltext`, '[0-9][0-9]') AS `digits` FROM `source`

Additional Criteria (Numbers > 20)

To add the criterion that the numbers should be greater than 20, simply add this condition to the WHERE clause of either query:

WHERE `digits` > 20

The above is the detailed content of How to Extract Consecutive Digits from a MySQL Text Field and Apply Additional Criteria?. For more information, please follow other related articles on the PHP Chinese website!