MySQL Forums
Forum List  »  Newbie

Re: Extract the found REGEXP value
Posted by: Devart Team
Date: November 17, 2010 02:38AM

Function definition -

DELIMITER $$
CREATE FUNCTION function_get_digits(input_string VARCHAR(255))
RETURNS VARCHAR(255) CHARSET utf8
BEGIN
  SET @digit_len = 9; -- no more digits are parsed
  SET @digit_pos = -1;
  SET @len = LENGTH(input_string);
  SET @i = 1;

  WHILE @i <= @len
  DO
    SET @c = SUBSTRING(input_string, @i, 1);
    IF ASCII(@c) > 47 AND ASCII(@c) < 58 THEN
      IF @digits_pos = -1 THEN
        SET @digits_pos = @i;
      END IF;
      IF @i - @digits_pos + 1 = @digit_len THEN
        RETURN SUBSTR(input_string, @digits_pos, @digit_len);
      END IF;
    ELSE
      SET @digits_pos = -1;
    END IF;

    SET @i = @i + 1;
  END WHILE;

  RETURN NULL;
END$$
DELIMITER ;

Check result -

SET @input_string = 'Finance Group = FIN, ACCT, COST, PRC\Steven Smith 210053513';
SET @ResultValue = function_get_digits(@input_string);
SELECT @ResultValue;
+--------------+
| @ResultValue |
+--------------+
| 210053513    |
+--------------+

Devart Company,
MySQL management tools
http://www.devart.com/dbforge/mysql/

Options: ReplyQuote


Subject
Written By
Posted
November 15, 2010 03:00PM
Re: Extract the found REGEXP value
November 17, 2010 02:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.