converting a text field to number

[Ciar?n]

I have a table with over a million rows and one of the fields contains
amounts of money in text format.
What is the most efficient way of converting this field to a number
format that I can sum on?

Regards,
Ciarán

[Nazeer Oasis]

Hi Ciaran,

Before converting the type of the col, make sure that there are no
invalid values in that column. You can pull them out by

SELECT colName FROM tabName WHERE IsNumeric(colName) = 0

Alter your column type by

ALTER TABLE tableName ALTER COLUMN colName NUMERIC
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Nazeer Oasis (na******@indiatimes.com) writes:

Before converting the type of the col, make sure that there are no
invalid values in that column. You can pull them out by

SELECT colName FROM tabName WHERE IsNumeric(colName) = 0
Alter your column type by

ALTER TABLE tableName ALTER COLUMN colName NUMERIC

Unfortunately, this may still fail, since IsNumeric will approve of
values than converts to float or money, but not to numeric. Also, I
say that it's extremely bad practice to say numeric without specifying
scale and precision. You get some defaults, but these may not be what
you expect.

As for the original query, the easy way is:

SELECT SUM(convert(int, textcol)) FROM tbl
or SELECT SUM(convert(money, textcol)) FROM tbl

But this will of course fail if there are strings that does not convert.

If all data is integer, that is the text is undelimited and there are
no decimals, then it's pretty easy to test:

textcol NOT LIKE '%[0-9]%'

If the text can delimiters and decimals, it can become quite hairy
to filter.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Erland Sommarskog (es****@sommarskog.se) writes:

If all data is integer, that is the text is undelimited and there are
no decimals, then it's pretty easy to test:

textcol NOT LIKE '%[0-9]%'

This is wrong. I forgot a ^:

textcol NOT LIKE '%[^0-9]%'


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp