converting to numeric - 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 2000 Forums

Transact-SQL (2000)

converting to numeric

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-03-29 : 12:35:33

I have a field field1 of table1 which is varchar(50) and the values in there are NULL,'',25,45.98,56.89 and now I want to get the sum of all of them and then insert into table2 and field1 which is numeric 5(6,3).Please help me with the select query for this

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-29 : 12:45:57

what number should Null and '' convert to? You need to be specific with your requirements.

- Jeff

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-03-29 : 13:12:35

The field1 of table1 consist of NULL and empty string values since it is varchar field.But I want to get the sum of them basically avoiding the nulls and empty values and get the values so that it can be inserted into another field in table 2 which is a numeric field.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-29 : 14:04:07

think logically about what you need to do to sum up the values ... you want to exclude '' and Null, right? how do we exclude things from a SELECT statement?


select 
  sum(convert(numeric(6,3),Field1)) as Total
from 
  YourTable
where
  Field1 is not null AND Field1 <> ''

- Jeff

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-03-29 : 16:00:16

On a completely pedantic tangent, you don't really need to exclude the Nulls since you are using the SUM aggregate.

(He did say that he wanted to learn...)

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-29 : 16:30:46

Since there may be other issues with non-numeric data in this varchar(50) column, this might be a good way to go:

select 
	sum(convert(numeric(6,3),Field1)) as Total
from 
	YourTable
where
	isnumeric(Field1) = 1

Of course, this does not address issues like a really big number, 99999999999999999, in this varchar(50) column which would overflow your numeric(6,3), but you will find out soon enough if you have a problem.

I won't ask why the numbers are stored in a varchar column.

CODO ERGO SUM

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-03-29 : 17:08:39

I just learned from someone else on another forum that the following strings can produce "false positives", or at least potentially unexpected results, with the ISNUMERIC function:

ISNUMERIC('4D0')
ISNUMERIC('5E3')
ISNUMERIC('.')
ISNUMERIC(',')
ISNUMERIC(',.')
ISNUMERIC('$1')

So one needs to be careful if they want strictly numerical data and don't want to include data that the ISNUMERIC function thinks is monetary data or scientific notation.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-29 : 17:30:38

It is certainly true that ISNUMERIC() has some holes in it. You can do a little more work to filter out some more stuff. Depending on the garbage level in your data, you may want to add more tests.

select 
	sum(convert(numeric(6,3),Field1)) as Total
from 
	YourTable
where 
	case
	-- Skip nulls
	when Field1 is null
	then 0
	-- Skip empty strings
	when rtrim(Field1) = ''
	then 0
	-- Allow only characters 0-9 or a decimal point,
	-- but don't worry about leading or trailing spaces.
	when ltrim(rtrim(Field1)) like '%[^0123456789.]%'
	then 0
	-- Finally, make sure it is numeric
	when isnumeric(Field1) = 1
	then 1
	else 0
	end = 1

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-29 : 17:52:47

quote:
Originally posted by Bustaz Kool

On a completely pedantic tangent, you don't really need to exclude the Nulls since you are using the SUM aggregate.

(He did say that he wanted to learn...)

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

true -- but he will get an ANSI warning indicating that Nulls were eliminated from the aggregate function ....

- Jeff

DClayton77
Starting Member

11 Posts

Posted - 2005-03-30 : 15:25:12

true - but the SET ANSI_WARNINGS OFF statement will eliminate that message. This is important when calling the sp from an ADO/ADO.Net command object.

You know, if there's one thing I've learned from being in the army, it's never ignore a pooh-pooh. I knew a major: got pooh-poohed; made the mistake of ignoring the pooh-pooh -- he pooh-poohed it. Fatal error, because it turned out all along that the soldier who pooh-poohed him had been pooh-poohing a lot of other officers, who pooh-poohed their pooh-poohs. In the end, we had to disband the regiment -- morale totally destroyed ... by pooh-pooh!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources