January 22, 2020 at 7:53 pm
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!
January 22, 2020 at 8:04 pm
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]
January 22, 2020 at 8:46 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 23, 2020 at 2:31 am
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
Change is inevitable... Change for the better is not.
January 23, 2020 at 12:21 pm
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
January 23, 2020 at 12:48 pm
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
January 23, 2020 at 12:51 pm
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]
January 23, 2020 at 1:14 pm
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
January 23, 2020 at 1:23 pm
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]
January 23, 2020 at 1:30 pm
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
January 23, 2020 at 2:42 pm
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;
January 23, 2020 at 3:02 pm
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
GOThere 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
Change is inevitable... Change for the better is not.
January 23, 2020 at 3:32 pm
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
Change is inevitable... Change for the better is not.
January 23, 2020 at 7:56 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 23, 2020 at 9:19 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply