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 
            if (p is null)
              return null;
            declare bigdecimal var = cast(p as bigdecimal);
            return true;
          exception e
            return false;


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

          1 of 1 people found this helpful