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 out of the box. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 27, 2016 at 14:08
Haven't tried it, but have you seen dotancohen's comment on the accepted answer in the SO question you linked ? That might work for you. — roberto06
– roberto06, Commented Jul 27, 2016 at 14:08
Why not use PHP to strip the numbers from string before running your query? — romellem
– romellem, Commented Jul 27, 2016 at 14:09

Naisa purushotham · Accepted Answer · 2016-07-27 14:10:44Z

3

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

answered Jul 27, 2016 at 14:10

Naisa purushotham

91310 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query only numbers from string in MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related