Re: Bad numeric data

On 14-May-2015 12:24 -0500, Luis Rodriguez wrote:

<<SNIP>> write a SQL function that checks if your number is valid.

Some years ago we had to write a small function in order to check
some bad data we had received from an external provider. Our function
was something like this :

----------------------------------------------------
DROP  Function QGPL/IsNumeric;

Create Function QGPL/IsNumeric (@TestData VarChar(64))
   Returns Char(1)
   Language SQL
   Deterministic
   Not Fenced
   Set Option Commit=*None, UsrPrf=*Owner

   BEGIN
    Declare @Double Double Not Null Default 0;

    Declare InvalidNum condition for '01565';
    Declare Exit Handler For InvalidNum
     Return 'N';
    If @TestData Is Null Then
     Return 'N';
    End If;

    Set @Double=Cast(@TestData As Double);
    Return 'Y';
   END
----------------------------------------------------

So you could something like:

UPDATE MyTable set MyField = 0
WHERE IsNumeric( cast(MyField  as varchar(64))  )  = 'N'