Tuesday, 8 March 2016

Check if string value is numeric

To test a string for numeric characters, you could use a combination of the LENGTH, TRIM, AND TRANSLATE functions in the following way:

LENGTH(TRIM(TRANSLATE('this_string', ' +-.0123456789', ' ')))

'this_string' is the string value that you are testing.

This function will return a null value if 'the_string' is numeric. It will return a number greater than 0 if 'the_string' contains any non-numeric characters.

Examples:

SQL Result
LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' '))) 1
LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' '))) 2
LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' '))) null
LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' '))) null

No comments:

Post a Comment