The IsNumeric() function takes one (1) to three (3) arguments and returns either 1 as true or 0 as false. It tests the given value in first parameter as numeric or against the given number style in second argument,
if thrid argument is specified the testing is against that particular culture. It can be written in simple english as Is this [Value] is [Format] in this [Culture]. The second and third parameter are
optional.
Syntax IsNumeric( Number Value , [ Number Style ] , [ Culture Name ] )
The argument [ Number Style ] can be one or combination of multiple seperated by pipe (|) sign as 'Integer|Float|AllowParentheses'
| Number Style |
Description |
| AllowCurrencySymbol |
Indicates that the numeric string can contain a currency symbol. |
| AllowDecimalPoint |
Indicates that the numeric string can have a decimal point. |
| AllowExponent |
Indicates that the numeric string can be in exponential notation. The AllowExponent flag allows the parsed string to contain an exponent that begins with the "E" or "e" character and that is followed by an optional positive or negative sign and an integer. In other words, it successfully parses strings in the form nnnExx, nnnE+xx, and nnnE-xx. It does not allow a decimal separator or sign in the significand or mantissa; to allow these elements in the string to be parsed, use the AllowDecimalPoint and AllowLeadingSign flags, or use a composite style that includes these individual flags. |
| AllowHexSpecifier |
Indicates that the numeric string represents a hexadecimal value. Valid hexadecimal values include the numeric digits 0-9 and the hexadecimal digits A-F and a-f. Strings that are parsed using this style cannot be prefixed with "0x" or "&h". |
| AllowLeadingSign |
Indicates that the numeric string can have a leading sign. |
| AllowLeadingWhite |
Indicates that leading white-space characters can be present in the parsed string. |
| AllowParentheses |
Indicates that the numeric string can have one pair of parentheses enclosing the number. The parentheses indicate that the string to be parsed represents a negative number. |
| AllowThousands |
Indicates that the numeric string can have group separators, such as symbols that separate hundreds from thousands. |
| AllowTrailingSign |
Indicates that the numeric string can have a trailing sign. |
| AllowTrailingWhite |
Indicates that trailing white-space characters can be present in the parsed string. |
| Any |
Indicates that all styles except AllowHexSpecifier are used. This is a composite number style. |
| Currency |
Indicates that all styles except AllowExponent and AllowHexSpecifier are used. This is a composite number style. |
| Float |
Indicates that the AllowLeadingWhite, AllowTrailingWhite, AllowLeadingSign, AllowDecimalPoint, and AllowExponent styles are used. This is a composite number style. |
| HexNumber |
Indicates that the AllowLeadingWhite, AllowTrailingWhite, and AllowHexSpecifier styles are used. This is a composite number style. |
| Integer |
Indicates that the AllowLeadingWhite, AllowTrailingWhite, and AllowLeadingSign styles are used. This is a composite number style. |
| None |
Indicates that no style elements, such as leading or trailing white space, thousands separators, or a decimal separator, can be present in the parsed string. The string to be parsed must consist of integral decimal digits only. This is also default if argument is not provided |
| Number |
Indicates that the AllowLeadingWhite, AllowTrailingWhite, AllowLeadingSign, AllowTrailingSign, AllowDecimalPoint, and AllowThousands styles are used. This is a composite number style. |
/* Following will return 0 */
SELECT IsNumeric('ABCD');
/* Following will return 1 */
SELECT IsNumeric(1234);
/* Following will return 0 */
SELECT IsNumeric('$123.45');
/* Following will return 1 */
SELECT IsNumeric('$123.45' , 'Currency');
/* Following will return 1 as not only integer but float and real are also tested. */
SELECT IsNumeric('123.45' , 'Integer|Float|Real');
/* Following will return 1 */
SELECT IsNumeric('$123.45' , 'Currency', 'en-US');
/* Following will return 0 due to dollar sign (not valid with en-GB) */
SELECT IsNumeric('$123.45' , 'Currency', 'en-GB');
/* Following will return 1 as '£123.45' which will be considered
currency in UK because of en-GB as culture. */
SELECT IsNumeric('£123.45' , 'Currency', 'en-GB');