1 Reply Latest reply on Jun 13, 2016 3:26 PM by Steven Hawkins

    checking a string whthere it is a valid number

    dineshnasa Newbie

      In Teiid, I need a function similar to isNumeric in sql

        • 1. Re: checking a string whthere it is a valid number
          Steven Hawkins Master

          There is no standard isnumeric function in SQL, but both SQL Server and Oracle do have such a function.

           

          In Teiid if the numerical value follows a simple regex pattern, then you could use LIKE_REGEX.  Otherwise you'd have to use a UDF or define a virtual function with exception handling using a cast -

           

          CREATE VIRTUAL FUNCTION isNumeric(p string) RETURNS boolean AS 
          begin
            if (p is null)
              return null;
            declare bigdecimal var = cast(p as bigdecimal);
            return true;
          exception e
          
            return false;
          end
          

           

          You can also log an issue to add isnumeric to the standard function library.

          1 of 1 people found this helpful