Check if a string ISNUMERIC()?

Help Procedural SQL
1

There is an inbuilt function ISNUMERIC() in SQL Server which checks and returns TRUE if the string has “only digits” (so it is a number) otherwise FALSE. Do we have a similar function in singlestore (memsql)?

Thanks
Azhar Ansari

2

We don’t currently have an ISNUMERIC function, I’ve filed an internal feature request for it.

In the meantime, you could use a UDF as a workaround. Alternatively the json_get_type function may work as a workaround, it should return double as long as it is numeric:

MySQL [(none)]> select json_get_type('1234abc');
+--------------------------+
| json_get_type('1234abc') |
+--------------------------+
| NULL                     |
+--------------------------+
1 row in set (0.18 sec)

MySQL [(none)]> select json_get_type('1234');
+-----------------------+
| json_get_type('1234') |
+-----------------------+
| double                |
+-----------------------+
1 row in set (0.29 sec)
3

Hi Rob,

Thank you for a quick response. I checked your solution but it does not work if the string we want to check has some leading or trailing SPACES (results below).

So, I have already created a UDF to achieve this functionality. Below is the UDF and the calling proc for your reference. It works perfectly and handles all the scenarios including leading/trailing SPACES.

**

But, there is a catch here too!!

**

The input to the UDF IS_NUMERIC() must only be of VARCHAR or TEXT type…!! If we try to give the input as CHAR then it does not handle the trailing SPACES issue.

I believe it is because of how Singlestore is passing the data from UDF to the calling proc.
When the input is of TEXT or VARCHAR type then it is passed as is.
But when the input is of CHAR type then the trailing spaces are trimmed and then passed to the calling proc.

I can say this because I tested the length of the sent input string to IS_NUMNERIC (but this time actually creating it as a proc so I could use ECHO SELECT in it to get the length).

Could you please see if this is a potential bug or is it the functionality that Singlestore has kept!

Finally, an in-built ISNUMERIC() function that handles all these scenarios would be great :slight_smile:

Apologies for such a long post!

> memsql> select json_get_type('1234 ');
> +------------------------+
> | json_get_type('1234 ') |
> +------------------------+
> | double                 |
> +------------------------+
> 
> memsql> select json_get_type(' 1234');
> +------------------------+
> | json_get_type(' 1234') |
> +------------------------+
> | double                 |
> +------------------------+

Below is the UDF and its calling proc.

DELIMITER //
CREATE OR REPLACE FUNCTION IS_NUMERIC(input VARCHAR(50)) RETURNS BOOL AS
DECLARE
result INT = REGEXP_SUBSTR(input,'[[:digit:]]',1,LENGTH(input));
BEGIN
IF result IS NOT NULL THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END //
DELIMITER ;

DELIMITER //
CREATE OR REPLACE PROCEDURE CallingProc() RETURNS VOID AS
DECLARE
input VARCHAR(50) = '4567  ';
BEGIN
IF IS_NUMERIC(input) IS TRUE THEN
ECHO SELECT "Only Digits..!!";
ELSE
ECHO SELECT "Have some characters..!!";
END IF;
END //
DELIMITER ;

CALL CallingProc();

Thanks
Azhar

1 Like
4

This is an old post, but just happened to notice it while I was looking for something else.

In the newer versions, there is a built in ISNUMERIC and it handles the leading / trailing spaces:

There’s now also TRIM(), LTRIM() and RTRIM() and updating the above example like this results in the expected handling of trailing and leading blank spaces as well as ’ ’ and ‘’.

value VARCHAR(50) = TRIM(input);
result INT = REGEXP_SUBSTR(value,‘[[:digit:]]’,1,LENGTH(value));

2 Likes