IsNumeric equivalent in SQL/400

[bryanschmiedeler]

Trying to find an easy way to tell if a field has numeric data. Other SQLs have a function like IsNumeric. Is there an equivalent in SQL/400?

Thanks.

[bryce4president]

Re: IsNumeric equivalent in SQL/400

Do you want to know for a field that is defined as an alpha field? Or if the field itself is a numeric field?

[bryanschmiedeler]

Re: IsNumeric equivalent in SQL/400

Good question. The field is defined as alpha but the customer number can sometimes be the first six characters, IF they are numeric. I need a statement that grabs those first six characters and if they are numeric converts them to numeric but if they are not (they are blank or alpha or alpha and numeric) then makes the field I am populating zero.

I found a way to do this if the field is blank and if it is all alpha by using this:

Select case when lhjrf1 = ' ' then 0
when trim(lhjrf1) < '0' then '0'
else cast(lhjrf1 as dec(6,0))
end
as cust,lhjnen,
lhdram,lhcram, segment

but I am afraid it might not work if the field has some crazy value like "0abd".

And I am curious that I can't find in any of my reference books or on the net an easy function to test for the type of value.

[FaStOnE]

Re: IsNumeric equivalent in SQL/400

Could you not do something like ...

WrkNum       6S  0    Inz
WrkNo          6          Inz
....

Monitor;
   WorkNum = %Dec( %SubSt( InField : 1 : 6 ) : 6 : 0 );
On-Error;
   WorkNo = %SubSt( InField : 1 : 6 );
EndMon; 

Or a variation there of??

[Mercury]

Re: IsNumeric equivalent in SQL/400

You can write some RPG UDF to check whether the related column is a 6 digit-field or not.

D digits          c                   '0123456789'
if %check( digits : lhjrf1 ) = 0; // lhjrf1 contains digits only

[bryanschmiedeler]

Re: IsNumeric equivalent in SQL/400

Yes, I think I could do these things - so the answer is that there is no SQL function to do this? The code I saw was a RPG UDF and PDP.

Bryan

[B.Hauser]

Re: IsNumeric equivalent in SQL/400

Hi,

there is no scalar function, but you can use a work around (or even write your own function:

Select Length(Trim(Translate(MyChar, '          ', '1234567890'))) as ChkDigits
from MyTable 

ChkDigits will return 0 if the string contains only digits and blanks, or a numeric value representing the number of other characters.

With this information it is quite easy to write your own isNumeric function:

Create Function MySchema/isNumeric (               
       ParAlpha VarChar(32))              
       Returns Integer                    
       Language SQL      
       Deterministic                      
       Reads SQL Data                     
       Called on NULL Input               
       Disallow Parallel                  
Return Length(Trim(Translate(ParAlpha, '          ', '1234567890'))); 

The function can be called as follows:

Select MyFld, isNumeric(MyFld)
From MyTable 

MyFld must be a character field with varying length. The function will not work for character fields with fixed length. For fields with fixed length, you have to overload the function, that means create a second function with the same name but with a CHAR parameter.

Birgitta

[Mercury]

Re: IsNumeric equivalent in SQL/400

Birgitta,
Excellent demo as always.

[cdsalas]

This answer is years after the question but in case someone researches the counterpart of ISNUMERIC in AS400 SQL in the year 2022, here's how I solve it.
Assuming you have a 10 byte alpha field FLDA that may contain numbers or alpha characters.
You can do the following SQL statement where you'll only process records where FLDA is numeric:

SELECT blah blah blah
FROM blah/blah
WHERE HEX(FLDA) >= 'F0F0F0F0F0F0F0F0F0F0'
AND HEX(FLDA) <= 'F9F9F9F9F9F9F9F9F9F9'
AND FLDA <> ' '

This will return records where FLDA is a number and not blanks.
The number of "F0" depends on the size of your field. I have a 10 byte field so I compare it to F0F0F0F0F0F0F0F0F0F0.