Checking Whether a Character is Numeric

In SSIS, checking whether a value is numeric has always been a bit painful. Unfortunately, the SSIS expression language doesn’t have an IsNumeric function. Jamie Thomson (blog | twitter) has a nice post here that explains one way to do this check in a script component.

However, there is a shortcut you can take if you only need to check a specific character to see if it’s numeric. You can use the following expression:

FINDSTRING("0123456789", [CharacterToCheck], 1) != 0

If this returns True, the character is numeric. False means it’s not numeric.

This works by checking for the existence of the character in the string that contains all the numeric characters. If it finds an occurrence, then the character must be numeric. Obviously, this has some limitations over a real IsNumeric function, but if you just need to check single characters, it’s a lot faster than writing a script.

2 Comments

  1. Bas says:

    Nice trick. But it only works with a single character.
    I assume that you work with strings in SSIS. How would you do that?

    Bas

  2. jwelch says:

    For strings, I use script. In Jamie’s post (linked above) he explains how to do that, and some of the comments on his blog talk about other methods. The other methods, though, can be a bit flaky, so script is my preferred approach.

Leave a Reply