MySQL strip non-numeric characters to compare

by

I realise that this is an ancient topic but upon googling this problem I couldn’t find a simple solution (I saw the venerable agents but think this is a simpler solution) so here’s a function I wrote, seems to work quite well.

DROP FUNCTION IF EXISTS STRIP_NON_DIGIT;
DELIMITER $$
CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255))
   RETURNS VARCHAR(255)
BEGIN
   DECLARE output   VARCHAR(255) DEFAULT '';
   DECLARE iterator INT          DEFAULT 1;
   WHILE iterator < (LENGTH(input) + 1) DO
      IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
         SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
      END IF;
      SET iterator = iterator + 1;
   END WHILE;
   RETURN output;
END
$$

More Related Contents:

  1. How to do a regular expression replace in MySQL?
  2. MySQL Split Comma Separated String Into Temp Table
  3. How to extract two consecutive digits from a text field in MySQL?
  4. How to extract the nth word and count word occurrences in a MySQL string?
  5. How to remove all non-alpha numeric characters from a string in MySQL?
  6. How to count words in MySQL / regular expression replacer?
  7. #1139 – Got error ‘repetition-operator operand invalid’ from regexp
  8. Does MySQL Regexp support Unicode matching
  9. How do I refer to capture groups in a MySQL regex?
  10. MySQL SELECT LIKE or REGEXP to match multiple words in one record
  11. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  12. How to regex in a MySQL query
  13. remove all numeric characters from column mysql
  14. mySQL regex in the where clause
  15. mysql error cant fix the error [duplicate]
  16. Why can’t this entry be added because of Foreign Key error?
  17. MySQL pivot row into dynamic number of columns
  18. How to handle fragmentation of auto_increment ID column in MySQL
  19. Groupwise maximum
  20. MySQL automatically cast/convert a string to a number?
  21. How to select rows that have current day’s timestamp?
  22. What’s the best database structure to keep multilingual data? [duplicate]
  23. LEFT JOIN only first row
  24. How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?
  25. MySQL connection over SSH tunnel – how to specify other MySQL server?
  26. How to determine which database is selected
  27. How to remove constraints from my MySQL table?
  28. Get total hours worked in a day mysql
  29. MySQL “ERROR 1005 (HY000): Can’t create table ‘foo.#sql-12c_4’ (errno: 150)”
  30. Dump only the data with mysqldump without any table information?

Leave a Comment