String validation by SQL... - IBM AS400

This is a discussion on String validation by SQL... - IBM AS400 ; In SQL, I need to validate a string to be sure there is not letter in the string. I want only numbers between 0 and 9. Ex: 'A123456789' is wrong '0123456789' is valid Is there any easy way to validate ...

+ Reply to Thread
Results 1 to 2 of 2

Thread: String validation by SQL...

  1. String validation by SQL...

    In SQL, I need to validate a string to be sure there is not letter in
    the string. I want only numbers between 0 and 9.

    Ex:

    'A123456789' is wrong
    '0123456789' is valid

    Is there any easy way to validate this validity ?
    One way we found is to validate whether each caracter is between 0 and
    9 with a substring, but it a bit compilcated for nothing.

    Thanks, have a great day !


  2. Re: String validation by SQL...

    The following clause defines criteria to include rows where FIELDA
    has only digit characters, because the TRANSLATE expression is changing
    all digits to blank, against which FIELDA is being compared.

    WHERE translate(FIELDA, ' ', '0123456789') = ' '

    Note that this does not resolve embedded spaces nor all blanks, so
    translating the TRIM() of the value to the character '0' may be more
    appropriate for testing for valid use in conversion to numeric.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    karl.harvey@gmail.com wrote:
    > In SQL, I need to validate a string to be sure there is not letter in
    > the string. I want only numbers between 0 and 9.
    >
    > Ex:
    >
    > 'A123456789' is wrong
    > '0123456789' is valid
    >
    > Is there any easy way to validate this validity ?
    > One way we found is to validate whether each character is between
    > 0 and 9 with a substring, but it a bit complicated for nothing.


+ Reply to Thread