sql server: is it possible to convert 1,123 to number?

Question

i have a field that is a varchar and the string is stored as "1,223" or "23,342,234"

they have comas!

is it possible to read them as numbers?

i dont like any of these answers. in mysql reading a varchar 1,123 and converting it to a number was no problem at all.

Answer 1

You could go via the MONEY datatype:

CAST( CAST( value AS MONEY ) AS INT )

When casting a VARCHAR to MONEY thousands separators are allowed. Also, implicit cast from MONEY to other numeric types is allowed, so you might be able to get away with

CAST( value AS MONEY )

Might be interesting to see relative performance, but I'd be surprised to see much difference. But... probably depends on your target data type - for integer types going via MONEY might be... expensive.

Update

Well, it was more different than I expected - see Denis's answer. I guess this reflects the cost of using a general-purpose function like REPLACE as compared to the more specialised CAST of VARCHAR to MONEY.

Answer 2

Just replace any commas with nothing and cast it as an int:

SELECT CAST(REPLACE(NumberString,',','') as INT)