MariaDB is number function

19 May,2020 by Jack Vamvas

Question: I am creating a View in MariaDB to  to streamline a recordset requested by an application. As part of the View - there is a requirement to create some CASE statements and check to assess whether the data is numeric. 

How can I create a function to check if the data is numeric?

Answer: MariaDB has plenty of Numeric functions -  which process Functions dealing with numerals, including ABS, CEIL, DIV, EXP, PI, SIN, etc - but you are looking for a function - which checks to see if some data is a number. 

MariaDB has good support for creating functions and using regular expressions to evaluate data. Here is an example of a function called "isnumber". The DETERMINISTIC keyword sets the function to to always return one result given a set of input parameters.

I won't go into a whole conversation about regular expressions - but MariaDB has support for regexp . From the MariaDB documentation "regexp performs a pattern match of a string expression expr against a pattern pat"

CREATE FUNCTION `isnumber`(val varchar(1024)) RETURNS tinyint(1)
DETERMINISTIC
return val regexp '^[0-9]+$'

 

Read more  on MariaDB

MariaDB Cheatsheet

MariaDB RLIKE condition


Author: Jack Vamvas (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on MariaDB is number function


dba-ninja.com