How to Convert from Varchar to Decimal

  • I am asking to see what I am doing wrong.

    I am trying to Convert or Cast from Varchar to Decimal while I am doing DTS, and am constantly getting this error:

    " Error converting data type varchar to numeric."

    Currently, I have this data mode:

    1) FROM Staging table:

    varchar / length 3

    2) TO Production table:

    decimal / length 5 / Precision 3, Scale 0

    I used this syntex to covert such as:

    INSERT INTO tableB

       FieldNameB

    SELECT

       CAST( FieldNameA AS decimal(3,0))

    FROM tableA

    I know SQL's Book on line mentions as Implicit conversion, and so this is the reason that it should automatically convert, but I don't have any clue

    what to do at this moment.

    Appreciate for any feedback.

  • At the moment, I would doubt that all the data coming in adheres to your specified format.  Run something like the following:

    SELECT * FROM tableA WHERE ISNULL (FieldNameA, '') NOT LIKE REPLICATE ('[0-9]', LEN (FieldNameA))

    My guess is that you'll come up with at least one value that is not in the expected format.

     

     

  • Hi Lee,

    I really appreciate for your reply.

    I tried that query, but no data is found.

    Should I try something like numeric or integer instead of decimal?

    Justin

     

     

     

     

  • Can you show some of the data?

    I think I could have given you a better query earlier.  Try:

    SELECT * FROM tableA WHERE ISNULL (FieldNameA, '') = '' OR ISNULL (FieldNameA, '') NOT LIKE REPLICATE ('[0-9]', LEN (FieldNameA))

  • Hi Lee,

    Thank you for your help.

    Yes, I found 4 null or empty data in that field.

    If that is the result, would that mean I cannot convert empty varchar data into

    decimal or numeric or integer by itself?

    Justin

     

     

     

     

     

  • Off the top of my head, I'm thinking NULL should have worked if the accepting table column allows NULL, and in any event if it didn't, that's a whole nother error message.  It seems more likely to me that you have spaces in your source.

    Either way, the following ought to take care of it:

    If the receiving column allows NULL:

    INSERT INTO tableB (FieldNameB)

      SELECT CASE ISNULL (FieldNameA, '')

               WHEN ''

               THEN NULL

               ELSE CAST (FieldNameA AS DECIMAL (3,0))

             END

      FROM tableA

    If the receiving column does not allow NULL:

    INSERT INTO tableB (FieldNameB)

      SELECT CAST (FieldNameA AS DECIMAL (3,0))

      FROM tableA

      WHERE FieldNameA IS NOT NULL

        AND FieldNameA != ''

        AND FieldNameA LIKE REPLICATE ('[0-9]', LEN (FieldNameA))

    T-SQL has an ISNUMERIC function, but for my tastes it is far too tolerant of non-numerics.  T-SQL needs an ISREALLYTRULYNUMERICHONESTTOGOD function.

     

     

  • Hi Lee,

    It worked!!!

    Thank you so much for your help.

    Justin

     

     

  • Lee Dise (10/5/2005)


    T-SQL has an ISNUMERIC function, but for my tastes it is far too tolerant of non-numerics. T-SQL needs an IS-REALLY-TRULY-NUMERIC-HONEST-TO-GOD function.

    I made a function for this and named it "isReallyNumeric" ! 😀

    Here you go:

    /********************************************************************

    Function Purpose:

    This function will return a 1 if the string parameter contains ONLY

    numeric (0-9) digits and will return a 0 in all other cases.

    ~ Created by: Gavin Broughton

    ~ Twitter: http://www.twitter.com/ukgav

    ~ Website: http://www.varchar.co.uk

    ********************************************************************/

    CREATE FUNCTION [dbo].[udf_isReallyNumeric](@inputstring VARCHAR(4000))

    RETURNS int

    BEGIN

    DECLARE @output int

    SELECT @output =

    (CASE WHEN NULLIF(@inputstring,'') /* If string is empty */

    NOT LIKE '%[^0-9]%'/* and LIKE numbers 0-9 (NOT LIKE double negative needed here) */

    THEN 1 ELSE 0 END)/* then return int 1 */

    RETURN @output

    END

    Enjoy! 🙂

    - UKGav

    - Twitter: http://www.twitter.com/ukgav

Viewing 8 posts - 1 through 7 (of 7 total)

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