ISNUMERIC

Syntax

ISNUMERIC (exp)

Arguments

• exp: any valid combination of symbols and operators that the SingleStore engine evaluates to obtain a single data value.

Return Type

Boolean

Remarks

The expression can either be a numeric expression or a string.

For numeric expressions:

• The function will return 1 for any expression which can be converted to a number. Any kind of integer, floating point number, scientific notation number or expression as input results in 1 as a return value. For example: SELECT ISNUMERIC(42*3); and SELECT ISNUMERIC(-.5E+040);

• NULL as an input will return 0.

For strings:

• Currency notation is considered a numerical expression for these currency signs: $, €, £, ¥. For example, SELECT ISNUMERIC('€1'); will return 1.

• Other currency signs will return 0.

• Comma (,) symbol is ignored. Strings like "1,000" or ",10,00" will be considered as numeric. However commas are forbidden in scientific notation. Any occurrence of commas in this case returns 0.

• Commas are not allowed before currency signs or mathematical signs.

• Period (.) can be used once in a number (significand), and can be used at the beginning of a number (significand). For example, ".100", "1.100", and "1100." are valid numeric strings and will return 1. Strings like ".10.0" and "1.100." will return 0;

• Periods are not allowed before currency signs or mathematical signs.

• Plus and minus signs (+ or -) may appear at the beginning of a number (significand) or an exponent in scientific notation. They may also appear before currency signs. Strings like "+.1e10", "+1e-10", and "+$110" are valid numerics and will return 1. Only one sign can be present in a number (significand) and one in the exponent. + or - can appear on either side of a currency sign: "+$110" and "$+110" are both valid and will return 1. "+$+110" will return 0.

• Spaces. Leading or trailing spaces are ignored. Space(s) may appear on any side of a plus/minus sign, currency sign, or both: "+ $110", " + $ 110", and " + .1e10" are all valid and will return 1. Spaces cannot split numbers and are not allowed in exponents: "+.1e 10", "+. 1e10", and "+.1 e10" will return 0.

Examples

SELECT ISNUMERIC('not a number');

+----------------------------------+
| SELECT ISNUMERIC('not a number') |
+----------------------------------+
|                                0 |
+----------------------------------+

SELECT ISNUMERIC(42);

+----------------------+
| SELECT ISNUMERIC(42) |
+----------------------+
|                    1 |
+----------------------+

SELECT ISNUMERIC('42');

+------------------------+
| SELECT ISNUMERIC('42') |
+------------------------+
|                      1 |
+------------------------+

SELECT ISNUMERIC('42nd');

+--------------------------+
| SELECT ISNUMERIC('42nd') |
+--------------------------+
|                        0 |
+--------------------------+

SELECT ISNUMERIC(NULL);

+------------------------+
| SELECT ISNUMERIC(NULL) |
+------------------------+
|                      0 |
+------------------------+