MySQL Numeric Functions

Some useful numeric MySQL functions I wrote (unless sourced elsewhere). When I created these I was originally looking to clean some phone numbers and format them, but found there was not a way to get only numeric characters from a string, let alone an IsNumeric function in MySQL.

IsNumeric()
Accepts varchar up to 255 in length.
Returns 1 if string is numeric; returns 0 if fails test. (Source)

DROP FUNCTION IF EXISTS IsNumeric;

CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint 
 RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

NumericOnly()
Accepts varchar up to 255 in length.
Returns only the characters from passed in string which are numeric. (Removes all non-numeric characters.)

DROP FUNCTION IF EXISTS NumericOnly;

CREATE FUNCTION NumericOnly (val VARCHAR(255)) 
 RETURNS VARCHAR(255)
BEGIN
 DECLARE idx INT DEFAULT 0;
 IF ISNULL(val) THEN RETURN NULL; END IF;

 IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 SET idx = LENGTH(val);
  WHILE idx > 0 DO
  IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
   SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
   SET idx = LENGTH(val)+1;
  END IF;
  SET idx = idx - 1;
  END WHILE;
  RETURN val;
 END;

FormatPhone()
Accepts varchar up to 255 in length.
Returns string with the 10 right-most digits from input string (padded on the left with zeros) in the format: (###) ###-####

  DROP FUNCTION IF EXISTS FormatPhone;

  CREATE FUNCTION FormatPhone (val VARCHAR(255))
   RETURNS VARCHAR(255)
 BEGIN
  SET val = RIGHT(CONCAT("0000000000",NumericOnly(val)),10);
  RETURN CONCAT("(",LEFT(val,3),") ",LEFT(RIGHT(val,7),3),"-",RIGHT(val,4));
 END;
Advertisements
This entry was posted in Functions, MySQL, Programming, Source Code. Bookmark the permalink.

12 Responses to MySQL Numeric Functions

  1. Sean says:

    The following line generates an error for me :
    WHILE idx >; 0 DO
    should this be:
    WHILE idx > 0 DO ?

  2. Pingback: Is there a way to get only the numeric elements of a string in mysql?

  3. hicham.077 says:

    nice Thank you ^^

  4. Deepinder says:

    This does not work. If i pass a value like ‘3445sde&&’

    SET idx = REPLACE(val,”.”,””);

    tries to replace a dot and assign it to idx which is an integer and it throws an error at that stage.

  5. ANTONY NAVIN says:

    Hi,
    I am not able to get the expected result.I am just getting ‘abc987’.
    Appreciate help!

    • andy370 says:

      Are you using mysql? Did you create both the “IsNumeric” and “NumericOnly” functions on your database?

      The result of:
      Select NumericOnly(“abc987”);
      is 987 for me.

  6. hillmandj says:

    I get a syntax error when creating your NumericOnly function. It just says that the error is at the final END; statement. Not sure why it’s broken, but have tried messing around in SQL pro and have not succeed

    • hillmandj says:

      figured it out, please be sure to alter your DELIMITER if you come across this issue:

      DROP FUNCTION IF EXISTS NumericOnly;
      DELIMITER $$
      CREATE FUNCTION NumericOnly (val VARCHAR(255)) RETURNS VARCHAR(255)
      BEGIN
      DECLARE idx INT DEFAULT 0;
      IF ISNULL(val) THEN RETURN NULL; END IF;

      IF LENGTH(val) = 0 THEN RETURN “”; END IF;

      SET idx = LENGTH(val);
      WHILE idx > 0 DO
      IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
      SET val = REPLACE(val,SUBSTRING(val,idx,1),””);
      SET idx = LENGTH(val)+1;
      END IF;
      SET idx = idx – 1;
      END WHILE;
      RETURN val;
      END$$
      DELIMITER ;

  7. rudolf says:

    Hi,
    Do not use LENGTH in this function! This calculate bytes, not characters. This is important if u use utf8 encoding. Use char_lenght instead.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s