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]

Re: converting a text field to number


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(colNa me) = 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]

Re: converting a text field to number

Nazeer Oasis (nazeerpp@india times.com) writes:[color=blue]
> 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(colNa me) = 0
> Alter your column type by
>
> ALTER TABLE tableName ALTER COLUMN colName NUMERIC[/color]

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(mon ey, 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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: converting a text field to number

Erland Sommarskog (esquel@sommars kog.se) writes:[color=blue]
> 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]%'[/color]

This is wrong. I forgot a ^:

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




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp