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

Depends on the Windows regional configuration settings AFAIK.

Answer 2

select Cast(Replace(myfield, ",","") as int)

Answer 3

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

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

Answer 4

If your existing select statement looks like this:

select field1 from table

You can use this instead to convert filed1 to a number:

select cast(replace(field1, ',', '') as int)  from table

Answer 5

Just strip out the commas and do a cast:

SELECT CAST(REPLACE('12,345', ',', '') AS DECIMAL(32,2))

Answer 6

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 7

I'm sure you are all interested in the performance side of this issue.

Stress testing of cast-as-money-then-as-bigint approach.

Filling test data in a quick-and-dirty way:

;with ids as
(
select cast(abs(checksum(newid())%1000) as varchar) [1]
,cast(abs(checksum(newid())%1000) as varchar) [2]
,cast(abs(checksum(newid())%1000) as varchar) [3]
union all
select cast(abs(checksum(newid())%1000) as varchar) 
,cast(abs(checksum(newid())%1000) as varchar) 
,cast(abs(checksum(newid())%1000) as varchar) 
from ids
)
, enum as (
select [1]+','+[2]+','+[3] n, row_number() over(order by (select 0)) rn 
from ids
where len([1]) = 3 and len([2]) = 3 and len([3]) = 3
)
select rn, n
into temp.[money]
from enum
where rn <= 1000000
option (maxrecursion 0)

Now it's time to stress test our initial approach when we want to remove all the commas from a string:

select min(mn), max(mn), avg(mn)
from (
select cast( replace(n,',','') as bigint) mn
from temp.money
) m

It takes on average 10.6 seconds to complete!

And, finally, this via-money approach:

select min(mn), max(mn), avg(mn)
from (
select cast(cast(n as money) as bigint) mn
from temp.money
) m

It takes on average 1.5 seconds to complete!