Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

sql - Sum of data in varchar column

i have to sum element in a column(SCENARIO1) that is varchar and contain data like (1,920, 270.00, 0, NULL) but when i try to convert data into int or decimal i get this error : "he command is wrong when converting value "4582014,00" to int type"

here is my request :

select sum( convert( int, SCENARIO1) )
from Mnt_Scenario_Exercice where code_pere='00000000'

any help please

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

try this

select sum(cast(replace(SCENARIO1, ',', '.') as decimal(29, 10)))
from Mnt_Scenario_Exercice
where code_pere = '00000000';

If you couldn't convert your '4582014,00' into decimal, there's a chance you have different decimal separator on your server. You could look what it is or just try '.'


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...