How do you extract a numerical value from a string in a MySQL query?

by

This function does the job of only returning the digits 0-9 from the string, which does the job nicely to solve your issue, regardless of what prefixes or postfixes you have.

http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#815

Copied here for reference:

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS digits;
DELIMITER |
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 |
DELIMITER ;

SELECT digits('$10.00Fr'); 
#returns 1000

More Related Contents:

  1. SQL order string as number
  2. How to convert a string to date in MySQL?
  3. How to find similar results and sort by similarity?
  4. How do I do a fuzzy match of company names in MYSQL with PHP for auto-complete?
  5. How to remove all non-alpha numeric characters from a string in MySQL?
  6. How to escape single quotes in MySQL
  7. MySQL – length() vs char_length()
  8. Capitalize first letter. MySQL
  9. Can MySQL replace multiple characters?
  10. How to fix double-encoded UTF8 characters (in an utf-8 table)
  11. MySql – Way to update portion of a string?
  12. mysql: why comparing a ‘string’ to 0 gives true?
  13. MySQL : how to remove double or more spaces from a string?
  14. Strip last two characters of a column in MySQL
  15. MySql Query Replace NULL with Empty String in Select
  16. Last index of a given substring in MySQL
  17. How to get maximum number of products matched vendors list. But i don't want to check same products again [closed]
  18. How to select the nth row in a SQL database table?
  19. MySQL – How to unpivot columns to rows?
  20. MySQL SELECT only not null values
  21. Difference between two dates in MySQL
  22. Scaling solutions for MySQL (Replication, Clustering)
  23. MySQL show status – active or total connections?
  24. mysql check if numbers are in a comma separated list
  25. mySQL DataSource on Visual Studio 2012
  26. MySQL update case help
  27. limiting the rows to where the sum a column equals a certain value in MySQL
  28. Mysql select distinct
  29. How would I use ON DUPLICATE KEY UPDATE in my CodeIgniter model?
  30. Transpose rows into columns in MySQL

Leave a Comment