CODEWITHSUNDEEP
phpmysql
user3358102
user3358102

Reputation: 317

How to query only numbers from string in MySQL

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 ";"

Upvotes: 1

Views: 7057

Answers (1)

Naisa purushotham
Naisa purushotham

Reputation: 913

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

Upvotes: 3

Related Questions