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:
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