ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

IsNumeric equivalent in SQL/400

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • IsNumeric equivalent in SQL/400

    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.

  • #2
    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?
    Your future President
    Bryce

    ---------------------------------------------
    http://www.bravobryce.com

    Comment


    • #3
      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.

      Comment


      • #4
        Re: IsNumeric equivalent in SQL/400

        Could you not do something like ...

        PHP Code:
        WrkNum       6S  0    Inz
        WrkNo          6          Inz
        ....

        Monitor;
           
        WorkNum = %Dec( %SubStInField ) : );
        On-Error;
           
        WorkNo = %SubStInField );
        EndMon
        Or a variation there of??

        Comment


        • #5
          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.
          Code:
          D digits          c                   '0123456789'
          if %check( digits : lhjrf1 ) = 0; // lhjrf1 contains digits only
          Philippe

          Comment


          • #6
            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

            Comment


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

              PHP Code:
              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:
              PHP Code:
              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:
              PHP Code:
              Select MyFldisNumeric(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

              Comment


              • #8
                Re: IsNumeric equivalent in SQL/400

                Birgitta,
                Excellent demo as always.
                Philippe

                Comment


                • #9
                  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.

                  Comment

                  Working...
                  X