使用分隔符将varchar值SQL转换为十进制， [英] Convert varchar value SQL to decimal with separator ,

问题描述

将varchar转换为double？



代码工作正常但没有值与分隔符的值，



例47,256.22



这个价值只是跳跃而没有列数额



我是什么尝试过：

选择基准，SUM（Try_Convert（float，[iznos_sa_pdv]））为UKUPNO 
来自mp_kasa_lista 
 group by datum 

解决方案

问题是您的号码中有千位分隔符。尝试替换逗号。例如

 选择 sum（try_convert（ float ，replace（ba，' ，'，'  '）））
 来自（
 选择 '  47,256.22'  as  a 
  union   all  
 选择 '  47,256.22'  as  a） as  b 

然而，长期修正将是使用列的数字数据类型，因为数据是数字。

引用：代码工作正常，但没有值与分隔符的总和

您刚刚体验过SQL数据库中存在数字数据类型的原因。
你刚才经历了为什么严肃的数据库管理员永远不会将数值存储到字符串字段中的原因。
通过将数字数据存储在字符串中，您可以在插入数据时使您的生活更轻松一次，但每次都让它变得更难你使用那些数据，它是一个选择。

Quote：

代码工作正常，但没有值与分隔符的和

在转换为float之前，你需要删除千位分隔符，如解决方案1中所示。



已经有37个Q& A问题！

您认为现在不是认真学习SQL基础知识吗？

SQL教程 [ ^ ]

Convert varchar to double?

Code working fine but no sum for values with separator ,

example 47,256.22

That value just jump and no have in column sum

What I have tried:

select datum, SUM (Try_Convert(float,[iznos_sa_pdv])) as UKUPNO
from mp_kasa_lista
group by datum

解决方案

The problem is that you have a thousands separator in the number. Try replacing the comma. For example

select sum( try_convert(float, replace(b.a, ',', '') ) )
from (
	select '47,256.22' as a
	union all
	select '47,256.22' as a) as b

However, the long term correction would be to use numeric data type for the column as the data is numeric.

Quote:

Code working fine but no sum for values with separator

You have just experienced the reason why numeric data types exist in SQL databases.
You have just experienced the reason why serious databases admins never store numeric values into string fields.
By storing numeric data in strings, you make your life easier 1 time when you insert the data, but you make it harder every time you use that data, it is a choice.

Quote:

Code working fine but no sum for values with separator

Before convert to float, you need to remove thousand separator as indicated in solution1.

Already 37 Q&A Questions !
Don't you think it is time to learn seriously SQL basics ?
SQL Tutorial[^]

这篇关于使用分隔符将varchar值SQL转换为十进制，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！