Find Conversion Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find Conversion Error

Author

Topic

andrewz00
Starting Member

15 Posts

Posted - 2009-06-17 : 12:51:09

i have a table full of test results and the field 'TestResultvalue' is varchar(200) it can contain just about anything.

one specific test should return only a number and i want to average that test result

avg(cast(testresultvalue as int)) returns an error because at some point records were entered with characters (ie 201. ) and the "." messes everything up..

Does anyone know a way to avoid these records that return errors?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-17 : 12:55:10

if '.' is problem, just use:-

avg(cast(replace(testresultvalue,'.','') as int))

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-17 : 12:56:00

There are several options. If the only data issue is having the period at the end, you could cast it to DECIMAL or even do a REPLACE on the "."[CODE]DECLARE @Table TABLE (testresultvalue VARCHAR(20))

INSERT @Table
SELECT '20'
UNION ALL SELECT '12'
UNION ALL SELECT '15.'
UNION ALL SELECT '19'
UNION ALL SELECT '6.6'

SELECT
avg(cast(cast(testresultvalue AS DECIMAL(18,0)) as int))
FROM @Table[/CODE]

andrewz00
Starting Member

15 Posts

Posted - 2009-06-17 : 13:13:19

well thats the one that stopped and returned the error. i suppose any character could be entered by accident. i do like the idea of casting as a decimal but what if its not a period? what if its a / or any other range of text... is there a way to just ignore the records that create the error?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-17 : 13:20:36

quote:
Originally posted by andrewz00

well thats the one that stopped and returned the error. i suppose any character could be entered by accident. i do like the idea of casting as a decimal but what if its not a period? what if its a / or any other range of text... is there a way to just ignore the records that create the error?

its possible

something like


avg(case when isnumeric(testresultvalue)=1 and charindex('e', testresultvalue) =0 and charindex('d', testresultvalue) =0 then cast(testresultvalue as int) else null end)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-17 : 14:25:54

Only you know what your business rules are, but an easy way it to just filter down to only integer values. However, if you need more complicated rules, you'll have to write some more coplicated logic (strange that. :)) Here are a couple examples:

DECLARE @Table TABLE (testresultvalue VARCHAR(20))

INSERT @Table
SELECT '20'
UNION ALL SELECT '12'
UNION ALL SELECT '15.'
UNION ALL SELECT '19'
UNION ALL SELECT '6.6'
UNION ALL SELECT 'foo'
UNION ALL SELECT '6/6'


-- Simple INT only filter
SELECT
	AVG(CAST(testresultvalue AS INT))
FROM
	@Table
WHERE
	testresultvalue NOT LIKE '%[^0-9]%'


-- More Complicated - Allows Integers and Decimals
SELECT
	AVG( 
		CAST
		(
		CASE
			WHEN testresultvalue NOT LIKE '%[^0-9]%' 
				THEN testresultvalue
			WHEN CHARINDEX('.', testresultvalue) > 1 
				THEN
					CASE 
						WHEN LEFT(testresultvalue, CHARINDEX('.', testresultvalue) - 1) NOT LIKE '%[^0-9]%' 
							THEN LEFT(testresultvalue, CHARINDEX('.', testresultvalue) - 1)
						ELSE 
							NULL 
					END
			ELSE 
				NULL
		END
		AS INT )
		)
FROM @Table
WHERE
	CASE 
		WHEN testresultvalue NOT LIKE '%[^0-9]%' THEN 1
		WHEN CHARINDEX('.', testresultvalue) > 1 THEN
			CASE WHEN LEFT(testresultvalue, CHARINDEX('.', testresultvalue) - 1) NOT LIKE '%[^0-9]%' THEN 1
			ELSE 0 END
		ELSE 0
	END	 = 1

andrewz00
Starting Member

15 Posts

Posted - 2009-06-17 : 14:39:23

Lamprey thank you very much! Love the "complicated" solution! works like a charm!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-18 : 05:22:56

quote:

Originally posted by Lamprey

Only you know what your business rules are, but an easy way it to just filter down to only integer values. However, if you need more complicated rules, you'll have to write some more coplicated logic (strange that. :)) Here are a couple examples:

DECLARE @Table TABLE (testresultvalue VARCHAR(20))

INSERT @Table
SELECT '20'
UNION ALL SELECT '12'
UNION ALL SELECT '15.'
UNION ALL SELECT '19'
UNION ALL SELECT '6.6'
UNION ALL SELECT 'foo'
UNION ALL SELECT '6/6'


-- Simple INT only filter
SELECT
	AVG(CAST(testresultvalue AS INT))
FROM
	@Table
WHERE
	testresultvalue NOT LIKE '%[^0-9]%'


-- More Complicated - Allows Integers and Decimals
SELECT
	AVG( 
		CAST
		(
		CASE
			WHEN testresultvalue NOT LIKE '%[^0-9]%' 
				THEN testresultvalue
			WHEN CHARINDEX('.', testresultvalue) > 1 
				THEN
					CASE 
						WHEN LEFT(testresultvalue, CHARINDEX('.', testresultvalue) - 1) NOT LIKE '%[^0-9]%' 
							THEN LEFT(testresultvalue, CHARINDEX('.', testresultvalue) - 1)
						ELSE 
							NULL 
					END
			ELSE 
				NULL
		END
		AS INT )
		)
FROM @Table
WHERE
	CASE 
		WHEN testresultvalue NOT LIKE '%[^0-9]%' THEN 1
		WHEN CHARINDEX('.', testresultvalue) > 1 THEN
			CASE WHEN LEFT(testresultvalue, CHARINDEX('.', testresultvalue) - 1) NOT LIKE '%[^0-9]%' THEN 1
			ELSE 0 END
		ELSE 0
	END	 = 1

The simplified version is


select avg(cast(cast(testresultvalue AS DECIMAL(18,0)) as int)) from
(
SELECT
	testresultvalue
FROM
	@Table
WHERE
	testresultvalue NOT LIKE '%[^0-9.]%'
) as t where isnumeric(testresultvalue)=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources