How to query only numbers from string in MySQL

Question

I have a MySQL query and need to ask for a field which contains the ZIP code. Unfortunately some people enter also the city name or suburb into the ZIP field. I was looking for a query code which would return only the numbers from the field. Any code I already found on stackoverflow producec errors for me besides the following: How to remove all non-alpha numeric characters from a string?

This code however only deletes no alphanumeric characters. What would be the correct code to also remove all a,b,c characters.

Thank you for helping

UPDATE: The code posted by Syed Noman works for me in phpmyadmin.

However when I add this to my query in my php code I get a parsing error.

Here is the code which produces the error.

$query   = "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(`asdf`) FROM `12345` WHERE 1 ";

The error indicates a problem with the last ";"

I think you will need a user-defined function for this, because [MySQL does not support regex replace](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) out of the box. — Tim Biegeleisen, Jul 27 '16 at 14:08
Haven't tried it, but have you seen [dotancohen's comment](http://stackoverflow.com/questions/6942973/mysql-how-to-remove-all-non-alpha-numeric-characters-from-a-string#comment39620969_22903586) on the accepted answer in the SO question you linked ? That might work for you. — roberto06, Jul 27 '16 at 14:08
Why not use PHP to strip the numbers from string before running your query? — romellem, Jul 27 '16 at 14:09

score 3 · Answer 1 · answered Jul 27 '16 at 14:10

USE REGEXP for getting only numbers

SELECT * 
FROM your_table 
WHERE zipcode REGEXP '^[0-9]+$';

or

SELECT * 
FROM your_table 
WHERE zipcode > 0

Hope it will help expected output Your zipcode column contains

123
145adb
adds142
157
237

output will be

123
157
237

How to query only numbers from string in MySQL

1 Answers1