Post reply

convert error varchar to float

  • August 3, 2006 at 6:14 am

    #113124

    I am trying to convert a value from varchar to float, and i am getting an error showing below. Any help please?

     

    CREATE FUNCTION Conv_Char_Float

    (@Inp varchar(255))

    RETURNS float(8)

    AS

    BEGIN

    declare @Return float

    if isnumeric(@Inp)=1

     select @return=@Inp

    else

     select @return=convert(float,@Inp) 

     

     

    return @return

    end

    __________________________________________________________

    Server: Msg 8114, Level 16, State 5, Procedure Conv_Char_Float, Line 12

    [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to float.

  • August 3, 2006 at 6:26 am

    #653051

    I think you are running into the ISNUMERIC issue where it returns 1 even when the number is not strictly numeric.

    ISNUMERIC allows certain non-numeric characters to be identified as numbers.

    E = Exponential

    D = (Something to do with FORTRAN).

    Try PATINDEX('%[^0-9.]%',@Inp)=0 instead of ISNUMERIC

    LinkedIn Profile

  • August 3, 2006 at 6:47 am

    #653063

    I 've got the same error

  • August 4, 2006 at 12:29 am

    #653253

    The varchar value i wanted to convert had the char ',' and that was the cause of  the problem. So i replace the ',' to '.' and the convert function is working fine.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply