Error converting data type varchar to float

  • Mark Eckeard

    Hall of Fame

    Points: 3462

    All,

    I know this topic has been discussed numerous times and I tried all possible solutions but to no avail, so here I am.

    I have an app that stores values like this in a varchar(7) column called Utilization in a table called CC_Agent_State_Summary :

    0.02%

    16.2%

    99.91%

    The app does nothing but store and display these values 1 at a time but I am tasked with reporting outside of the app.  Since I have to SUM and average these value for multiple records, I am getting the usual error:

    Error converting data type varchar to float

    I've trimmed the values, removed the '%' and I still cannot convert or cast these values.  Since I can't change the table structure, I am stuck doing this in my sproc.  That I can control but nothing I do works.

    Any thoughts?

    Thanks!

  • Erland Sommarskog

    SSC-Insane

    Points: 23896

    The first reply is: don't store data like this! Yes, I know you told us that you can't change the model, but pass the bucket: don't store data like this.

    Next, use try_cast: try_cast(substring(col, 1, len(col) - 1) as float). This will give you NULL for the values that will not convert.

    OK, since this is an SQL 2008 forum, you may reply that you don't have access to try_cast. This leads to the third piece of advice: Upgrade! SQL 2008 is out of support.

    Now, you may reply that it is not your powers to decide about upgrades. Well, tell the powers that be that you cannot run this query on SQL 2008, so you will need to export the data to an instance that runs SQL 2012 or later. This problem was not fun when SQL 2008 was all we had, and it is even less fun today when there actually is an acceptable solution. You can try isnumeric, but that is not really guarantee, since isnumeric may return 1 for a value that can convert to money, but not too float.

     

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • ScottPletcher

    SSC Guru

    Points: 98494

    OK, let's make a couple of quick fixes and then do a quick check for a couple of common errors, and see if we can find any bad data after these corrections have been made.

    SELECT DISTINCT TOP (100) Utilization
    FROM dbo.CC_Agent_State_Summary
    CROSS APPLY (
    SELECT REPLACE(Utilization, '%', '') +
    CASE WHEN Utilization LIKE '%.%' THEN '' ELSE '.0' END AS Utilization1
    ) AS ca1
    CROSS APPLY (
    SELECT CASE WHEN Utilization1 LIKE '%.%.%' OR Utilization1 LIKE '%[^0-9.]%'
    THEN 1 ELSE 0 END AS Utilization_has_bad_format
    ) AS ca2
    WHERE Utilization_has_bad_format = 1

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeff Moden

    SSC Guru

    Points: 996862

    Mark Eckeard wrote:

    I've trimmed the values, removed the '%' and I still cannot convert or cast these values.  Since I can't change the table structure, I am stuck doing this in my sproc.  That I can control but nothing I do works.

    Any thoughts?

    Yes.  Please post the code you tried that is giving you the error.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Mark Eckeard

    Hall of Fame

    Points: 3462

    Erland - This is a 3rd party app and I have no control over it.  Also, I have been asking for an upgrade from 2008 for 2 years and it was finally approved but I won't have that for a few more weeks.  In the meantime, I need to get this resolved.

    Scott - I have 2061 records and your query returned 2015 of them.  That's a difference of 46 records and that happens to be the number of records with NULL for Utilization.  So it's eliminating those.  It may be worth noting that some of the earliest values do not have a % after the value (ex 14.21 vs. 14.21%) so I assume the vendor changed the data type early on.

    Jeff - sorry, I try to keep my posts light but also know it can leave put pertinent info.  Here is a sample of what I tried:

    select SUM(CAST(ISNULL(REPLACE(LTRim(RTrim(Utilization)), '%', ''), 0) as FLOAT))

    from dbo.CC_Agent_State_Summary

    I also tried to get them in to a temp table with a float data type, like this:

    declare @newTable table

    (

    Utilization float

    )

    insert into @newTable(Utilization)

    SELECT CAST(ISNULL(REPLACE(LTRim(RTrim(Utilization)), '%', ''), 0) as FLOAT)

    FROM dbo.CC_Agent_State_Summary

    None of this works.

    Thanks for all the help.

    Mark

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    Mark

    That code works for the values you mentioned in your original post.  Perhaps you have one or more values in the column that, for example, uses a comma for the decimal point?

    SELECT Utilization
    FROM dbo.CC_Agent_State_Summary
    WHERE Utilization NOT LIKE '%[0-9].[0-9]%[%]'

    John

  • Erland Sommarskog

    SSC-Insane

    Points: 23896

    Copy the table to an instance of a newer version of SQL Server. Download and install Express Edition if needed. On that version run a query with try_cast, to see where you get values back and where you do not. Then we can take it from there. Or just hard-code for those bad values, since code only will five for a few weeks.

    As for the code you posted, it's probably better to have the trim operations outside the replace. Then again, they should not really matter anyway.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Mark Eckeard

    Hall of Fame

    Points: 3462

    John - I scanned the values and ran a query to check for any non-numeric values outside of '%' and nothing.  Your last query supports that as it returned the same 2015 values.

    Erland - I'll do that.

    I was talking about this to a co-worker that uploads the file with these values and found out there are 6 other fields with "%".  While I don't have to report on those other 6 fields currently I suspect that will happen soon.  I'm going to look in to creating a new upload page that will strip our the non-numeric characters and put them in a second table with the correct data type columns but would love to still figure out a solution since it's bothering me.

    Mark

  • Erland Sommarskog

    SSC-Insane

    Points: 23896

    While I may point out the obvious, when you run the query with TRY_CAST make sure that you also have the result of your massaging included, so that you can see what you are dealing with.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Steve Collins

    SSC Eights!

    Points: 893

    What happens if you add TOP(1)?  Will any row convert or does every row fail?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Eirikur Eiriksson

    SSC Guru

    Points: 182453

    A bit of an odd problem, tried few different cast/conversions and all are working fine on 2017/2019 (don't have 2008 on the laptop)

    😎

    Here is the code, no errors but the only value that fails is the one with CHAR(0) prefix.

    USE TEEST;
    GO
    --
    SET NOCOUNT ON;

    DECLARE @SAMPLE TABLE (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, NVAL VARCHAR(7) NOT NULL);
    INSERT INTO @SAMPLE(NVAL) VALUES ('0.02%'),('16.2%'),('99.91%'),(' 95.92%'),('96.93 %'),(CHAR(0)+'97.94%'),('97.94%'+CHAR(0));

    SELECT
    S.ID
    ,S.NVAL
    ,CONVERT(FLOAT,REPLACE(S.NVAL,'%',''),0) AS CONFLOAT0
    ,CONVERT(FLOAT,REPLACE(S.NVAL,'%',''),1) AS CONFLOAT1
    ,CONVERT(FLOAT,REPLACE(S.NVAL,'%',''),2) AS CONFLOAT2
    ,CONVERT(FLOAT,REPLACE(S.NVAL,'%',''),3) AS CONFLOAT3
    ,CAST(REPLACE(S.NVAL,'%','') AS FLOAT) AS CANFLOAT
    FROM @SAMPLE S;
  • Jeff Moden

    SSC Guru

    Points: 996862

    First of all, the LTRIM/RTRIM thing might just be a waste of clock cycles... the following works just fine.

     SELECT CAST(ISNULL(REPLACE(SPACE(10)+'16.3%'+SPACE(10), '%', ''), 0) as FLOAT);

    I suspect the problem you're fighting is "non-printable ASCII control codes".  For example, all of these return an error.

    SELECT CAST(ISNULL(REPLACE(LTRim(RTrim('16.3%'+CHAR(10))), '%', ''), 0) as FLOAT); --NewLine Character
    GO
    SELECT CAST(ISNULL(REPLACE(LTRim(RTrim('16.3%'+CHAR(9))), '%', ''), 0) as FLOAT); --Tab Character
    GO
    SELECT CAST(ISNULL(REPLACE(LTRim(RTrim('16.3%'+CHAR(13))), '%', ''), 0) as FLOAT); --Carriage Return Character
    GO
    SELECT CAST(ISNULL(REPLACE(LTRim(RTrim('16.3%'+CHAR(160))), '%', ''), 0) as FLOAT); --Non-Breaking Space Character
    GO

    There is an easy "super cheater" method to fix such a problem (provided that you have no more than 4 decimal places)...

    --===== Money strips out most "bad"/control characters
    SELECT CAST(ISNULL(CONVERT(MONEY,REPLACE('16.3%'+CHAR(10)+CHAR(9)+CHAR(13)+CHAR(160), '%', '')), 0) as FLOAT);

    And now you know why I asked for the code.  It provided the hint I needed to think that it was hidden control characters, which is a very common problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996862

    Ah... one more thing... REPLACE can make sucking sounds for performance even if the default collation is used.  It's performance can usually be increased by quite a bit when you're looking for non-alpha characters to replace by using a binary collation in the formula, as in the following (obviously, not totally tested because I don't have your data table)...

     SELECT  OriginalString  = Utilization
    ,FloatConversion = CONVERT(FLOAT,ISNULL(CONVERT(MONEY,REPLACE(Utilization COLLATE Latin1_General_BIN,'%','')),0))
    FROM dbo.CC_Agent_State_Summary
    ;

    Of course, all of this is contingent on my guess of it being a control character problem. 😀

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeffrey Williams

    SSC Guru

    Points: 88605

    If it is an issue of trailing control characters - you could also use SUBSTRING or LEFT to truncate the string and convert:

    SELECT cast(substring('16.3%'+CHAR(10), 1, charindex('%', '16.3%'+CHAR(10), 1) - 1) As FLOAT)
    SELECT cast(left('16.3%'+CHAR(10), charindex('%', '16.3%'+CHAR(10), 1) - 1) As FLOAT)

    If the problem is leading control characters - it gets more difficult, as you have to replace all non-numeric values.  If there only a couple of known control characters in the data it isn't too bad, but if there is a large number of control characters it becomes much more difficult.

    Now - if you are in a position to pre-process the data, or it is uploaded through something like SSIS or C# code then you have access to regex expressions and can strip the bad characters from the data before it is uploaded to SQL Server.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 996862

    Jeffrey Williams wrote:

    If it is an issue of trailing control characters - you could also use SUBSTRING or LEFT to truncate the string and convert:

    SELECT cast(substring('16.3%'+CHAR(10), 1, charindex('%', '16.3%'+CHAR(10), 1) - 1) As FLOAT)
    SELECT cast(left('16.3%'+CHAR(10), charindex('%', '16.3%'+CHAR(10), 1) - 1) As FLOAT)

    If the problem is leading control characters - it gets more difficult, as you have to replace all non-numeric values.  If there only a couple of known control characters in the data it isn't too bad, but if there is a large number of control characters it becomes much more difficult.

    Now - if you are in a position to pre-process the data, or it is uploaded through something like SSIS or C# code then you have access to regex expressions and can strip the bad characters from the data before it is uploaded to SQL Server.

    If you have a limit of 4 or fewer decimal places, the MONEY conversion auto-magically takes care of both leading and trailing control characters.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 17 total)

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