How Can I Extract Numerical Values from Strings in MySQL Queries?

Obtaining Numerical Values from Strings in MySQL Queries

Encountering a mismatch between two columns containing prices, one user sought a solution to extract the numerical value from the string representation in the price_display column and update the corresponding price column.

One successful approach employed a function that specifically extracted digits from the string, thereby accommodating various prefixes or postfixes. This function, labeled 'digits,' was created as follows:

CREATE FUNCTION digits( str CHAR(32) ) RETURNS CHAR(32)
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE ret CHAR(32) DEFAULT '';
  DECLARE c CHAR(1);

  IF str IS NULL
  THEN 
    RETURN "";
  END IF;

  SET len = CHAR_LENGTH( str );
  REPEAT
    BEGIN
      SET c = MID( str, i, 1 );
      IF c BETWEEN '0' AND '9' THEN 
        SET ret=CONCAT(ret,c);
      END IF;
      SET i = i + 1;
    END;
  UNTIL i > len END REPEAT;
  RETURN ret;
END;

To demonstrate its effectiveness, consider the query:

SELECT digits('.00Fr');

The output would be '1000,' matching the expected numerical value. This customized function provides a reliable method for extracting numerical values from strings within MySQL queries, addressing the user's specific requirement.

The above is the detailed content of How Can I Extract Numerical Values from Strings in MySQL Queries?. For more information, please follow other related articles on the PHP Chinese website!