Changing Data Types

Question

Changing Data Types

bernie.perks

SSC Enthusiast

Points: 142
More actions
March 10, 2016 at 4:02 am

#316714

I have a varchar field containing some data I want to report on. The SELECT statement below selects rows that start with a number to 1 decimal point followed by a % sign. I'm trying to convert the number part to FLOAT, then, grouping the results by another field, Region, report on the average percentage number per region. The statement below works to show individual rows and the number, but however I try to use Avg, I get an error, mostly 'Error converting datatype varchar to float', even though the statement as it is below runs fine. Any advice?
SELECT CONTACT.REGION, Cast(Left([LegacyItems],CharIndex('%',[LegacyItems])-1) As Float) AS Share
FROM (LEGACYITEM INNER JOIN LEGACY ON LEGACYITEM.ADMITNAME = LEGACY.ADMITNAME) INNER JOIN CONTACT ON LEGACY.BENEFACTORSERIALNUMBER = CONTACT.SERIALNUMBER
WHERE Len([LegacyItems])>0 AND CharIndex('%',[LegacyItems])>0 AND CONTACT.REGION Is Not Null
GROUP BY CONTACT.REGION, Left([LegacyItems],CharIndex('%',[LegacyItems])-1)
Putting Avg() around Cast(Left([LegacyItems],CharIndex('%',[LegacyItems])-1) As Float) causes the error.

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

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 1

Please post a repro script (that is a script that includes CREATE TABLE statements for all tables involved, with all constraints and indexes; INSERT statements with sample data; and in this case the query that works and the modified query that throws the error).

You can simplify (and if you are looking at a 200-row table right now, then you SHOULD simplify). But do run the script yourself on an empty database before posting here to verify that it works without errors, and that it actually does reproduce your situation.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Are you sure that you don't have invalid strings that escape your validation? Something like 'asd%'

I tried to replicate the error but wasn't able to do it. This is my test which works with 2008+, but might be an example of what's valid.

SELECT --*,

AVG(Cast(Left([LegacyItems],CharIndex('%',[LegacyItems])-1) As Float)) AS Share

FROM (VALUES('12%'), ('1%'),('21.3 % '),('12'),(''), (NULL), ('as%'))x(LegacyItems)

WHERE Len([LegacyItems])>0

AND CharIndex('%',[LegacyItems])>0

GROUP BY

Left([LegacyItems],CharIndex('%',[LegacyItems])-1)

Without the Where clauses, it throws an error.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

bernie.perks SSC Enthusiast Points: 142 More actions · Answer 3

Many thanks Luis - you're absolutely right. I was querying the live db and a new record had been created subsequent to my initial check that escaped the validation (which I've now improved).

Bernie