locked
convert varchar to dec RRS feed

  • Question

  •  I'm trying to insert into a table where the field is defined as Dec(5,1). The input comes from a staging table
    where table same field is varchar(255). I have tried to convert(dec(5,1),fielda), but on Insert gets a conversion Error converting data type varchar to numeric.

    data:

    0.1
    9.9
    12.3

    Thanks.

    Tuesday, June 30, 2020 6:19 PM

Answers

  • You have a char(10) appended in your column.

    See this example:

    Create table test (myStubborn255Column varchar(256), colChar9 varchar(256)
    ,colChar10 varchar(256) 
    ,colChar13 varchar(256)  )
    Insert into test values('0.7', '0.7'+char(9), '0.7'+char(10), '0.7'+char(13))
    Select   myStubborn255Column
    , convert(varbinary(20), myStubborn255Column) 
    , convert(varbinary(20), colChar9) 
    , convert(varbinary(20), colChar10) 
    , convert(varbinary(20), colChar13) 
    from dbo.test
    
    
    Select  
    Try_cast(replace( replace(replace(myStubborn255Column,char(9),''),char(10),''),char(13),'') as decimal(5,1))
    ,Try_cast(replace( replace(replace(colChar9,char(9),''),char(10),''),char(13),'') as decimal(5,1))
    ,Try_cast(replace( replace(replace(colChar10,char(9),''),char(10),''),char(13),'') as decimal(5,1))
    ,Try_cast(replace( replace(replace(colChar13,char(9),''),char(10),''),char(13),'') as decimal(5,1))
    
    from test
    
    drop table test

    • Marked as answer by hart60 Thursday, July 2, 2020 1:59 PM
    Thursday, July 2, 2020 1:43 PM

All replies

  • What is your SQL Server version? Instead of using CONVERT you can try TRY_CAST or TRY_CONVERT function which would not produce an error, but will give you NULL if the column's value cannot be converted.

    BTW, I didn't get the same error using just these 3 rows:

    declare @t table (data varchar(255))
    insert into @t (data) values ('0.1'), ('9.9'), ('12.3');
    
    select *, convert(decimal(5,1), data) from @t
    Make sure to remove other possible characters and use LTRIM and RTRIM for the column's value before converting.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Tuesday, June 30, 2020 6:25 PM
    Tuesday, June 30, 2020 6:24 PM
  •  I did a fix for Ltrim and Rtrim in the Staging Table, but I still get the errors.

    Tuesday, June 30, 2020 6:36 PM
  • Did you get the actual offending value? Also, you didn't answer my question about your SQL Server version (SELECT @@Version) and you didn't tell me if you tried to eliminate the error by using TRY_CAST or TRY_CONVERT and then inspect the values that produced NULL.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 30, 2020 6:40 PM
  •  Sorry it's SQL2012

    Tuesday, June 30, 2020 6:43 PM
  • I don't remember which exact version TRY_CAST or TRY_CONVERT functions have been introduced to and from the documentation page it's hard to figure out.

    Anyway, try these functions using select statement and then examine where did you get NULL as a result of the conversion.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver15


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 30, 2020 6:55 PM
  •  Sorry it's SQL2012

    SQL SERVER 2012 and onwards introduced the following three functions which helps us to handle type conversion exception. These functions are:
    1. TRY_PARSE
    2. TRY_CONVERT
    3. TRY_CAST

    https://www.tech-recipes.com/rx/60629/use-try_cast-function-sql-server/


    Tuesday, June 30, 2020 7:26 PM
  •  I did a try_cast for dec(5,1) and everything came back NULL. Then I did a try_cast for nvarchar(10), and
    I received ALL values, but it won't load from Staging to Live table

    Tuesday, June 30, 2020 11:07 PM
  • Try

    select myValue, convert(varbinary(10), myValue) as InspectValues from dbo.myStagingTable

    and see what are the characters (may be there are some hidden characters such as carriage return).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 30, 2020 11:53 PM
  • Hi hart60,

    Please share a DDL and sample data population for a staging table, T-SQL.

    Tuesday, June 30, 2020 11:54 PM
  • Hi hart60,

    Please try to convert to float first and then to decimal.

    create table test
    (columnA varchar(255))
    
    insert into test values('0.1'),('0.9'),('12.3')
    
    select CAST(CAST(columnA AS FLOAT) AS decimal(5,1)) columnB 
    from test
    
    drop table test 

    /*0.1
    0.9
    12.3*/


     Best Regards,
     Echo


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 1, 2020 7:03 AM
  •  

     I tried this and still didn't work.

    select CAST(CAST(columnA AS FLOAT) AS decimal(5,1)) columnB


     The data in flat file that loads to staging is "Tab-Delimited"  so I used -r '0x0a' -t ' ' in my BCP. The staging table fields are all defined varchar(255). Then I run a Ltrim\Rtrim against staging before loading Live table. I have 3 fields in Live table defined as Dec(5,1) and I used convert(dec(5,1),field), but the last field in table won't load using his technique.
     Thanks.

    Thursday, July 2, 2020 12:19 AM
  • Did you try what I suggested? E.g. it may be extra tab at the end of last column, it may be other character you don't see. Try 

    select top (100) myStubborn255Column, convert(varbinary(20), myStubborn255Column) from dbo.myStagingTable

    and print content of both columns for a few records.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 2, 2020 12:32 AM
  • Hi hart60,

    Could you please share us  your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) along with your expected result? So that we’ll get a right direction and make some test.

     Best Regards,
     Echo


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 2, 2020 1:40 AM
  •  Data from above query:

    select top (100) myStubborn255Column, convert(varbinary(20), myStubborn255Column) from dbo.myStagingTable

    0.7 0x302E370D
    0.8 0x302E380D
    0.6 0x302E360D
    1.0 0x312E300D
    0.5 0x302E350D

     Thanks.

    Thursday, July 2, 2020 12:13 PM
  • Hi hart60,

    This thread has already 14 replies with no solution in sight.

    >> "...Please share a DDL and sample data population for a staging table, T-SQL...."

    It would be great if you could provide a minimal reproducible example:
    (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
    (2) What you need to do, i.e. logic.
    (3) Desired output based on the sample data in #1 above.
    (4) Your SQL Server version (SELECT @@version;)

    Thursday, July 2, 2020 12:35 PM
  • You have a char(10) appended in your column.

    See this example:

    Create table test (myStubborn255Column varchar(256), colChar9 varchar(256)
    ,colChar10 varchar(256) 
    ,colChar13 varchar(256)  )
    Insert into test values('0.7', '0.7'+char(9), '0.7'+char(10), '0.7'+char(13))
    Select   myStubborn255Column
    , convert(varbinary(20), myStubborn255Column) 
    , convert(varbinary(20), colChar9) 
    , convert(varbinary(20), colChar10) 
    , convert(varbinary(20), colChar13) 
    from dbo.test
    
    
    Select  
    Try_cast(replace( replace(replace(myStubborn255Column,char(9),''),char(10),''),char(13),'') as decimal(5,1))
    ,Try_cast(replace( replace(replace(colChar9,char(9),''),char(10),''),char(13),'') as decimal(5,1))
    ,Try_cast(replace( replace(replace(colChar10,char(9),''),char(10),''),char(13),'') as decimal(5,1))
    ,Try_cast(replace( replace(replace(colChar13,char(9),''),char(10),''),char(13),'') as decimal(5,1))
    
    from test
    
    drop table test

    • Marked as answer by hart60 Thursday, July 2, 2020 1:59 PM
    Thursday, July 2, 2020 1:43 PM
  •   

     That Did the trick for loading the data.

    Try_cast(replace( replace(replace(myStubborn255Column,char(9),''),char(10),''),char(13),'') as decimal(5,1))

     Many thanks for ALL replies!!!

    Thursday, July 2, 2020 1:59 PM
  • This is what I suspected right from the beginning that you had extra character at the end you could not see.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 2, 2020 2:39 PM
  • Hi hart60,

    Jingyang Li, thanks for the DDL and sample data population.

    SQL:

    -- DDL and sample data population, start
    /*
    1. All invisible TAB, Carriage Return, and Line Feed characters will be replaced with spaces.
    2. Then leading and trailing spaces are removed from the value. 
    3. Further, contiguous occurrences of more than one space will be replaced with a single space.
    */
    CREATE FUNCTION dbo.udf_tokenize(@input VARCHAR(MAX))
       RETURNS VARCHAR(MAX)
    AS
    BEGIN 
       RETURN (SELECT CAST('<r><![CDATA[' + @input + ']]></r>' AS XML).value('(/r/text())[1] cast as xs:token?','VARCHAR(MAX)'));
    END
    
    DECLARE @tbl TABLE
    (
        myStubborn255Column VARCHAR(256),
        colChar9 VARCHAR(256),
        colChar10 VARCHAR(256),
        colChar13 VARCHAR(256)
    );
    
    INSERT INTO @tbl (myStubborn255Column, colChar9, colChar10, colChar13)
    VALUES
    ('0.7', '0.7' + CHAR(9), '0.7' + CHAR(10), '0.7' + CHAR(13));
    -- DDL and sample data population, end
    
    -- just to see
    SELECT myStubborn255Column,
           CONVERT(VARBINARY(20), myStubborn255Column),
           CONVERT(VARBINARY(20), colChar9),
           CONVERT(VARBINARY(20), colChar10),
           CONVERT(VARBINARY(20), colChar13),
           CONVERT(VARBINARY(20), dbo.udf_tokenize(colChar9)) AS colChar9
    FROM @tbl;
    
    SELECT TRY_CAST(myStubborn255ColumnAS DECIMAL(5, 1)) AS myStubborn255Column
    	, TRY_CAST(dbo.udf_tokenize(colChar9) AS DECIMAL(5, 1)) AS colChar9
    	, TRY_CAST(dbo.udf_tokenize(colChar10) AS DECIMAL(5, 1)) AS colChar10
    	, TRY_CAST(dbo.udf_tokenize(colChar13) AS DECIMAL(5, 1)) AS colChar13
    FROM @tbl;
    

    Thursday, July 2, 2020 4:25 PM