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.
'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 !
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.
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
> 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.
> '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.[/color]