用数值连接'$' [英] Concatenate '$' with numeric value

问题描述

我的GridView的SelectCommand如下所示：

The SelectCommand for my GridView is as follows:

SELECT
  pubName AS Publication,
  COUNT(*) AS Total,
  SUM(CAST (price as INT)) AS Price 
FROM [SecureOrders]
WHERE DateTime >= DATEADD(day, -1, GETDATE())
GROUP BY pubName

我在做SUM AS Price位时，我想添加一个美元符号（$），所以我的数据显示为109美元，而不是109美元。我尝试只做'$'+ SUM，但当然这并不奏效。有没有办法解决这个问题？

Where I'm doing the SUM AS Price bit, I want to add a dollar sign ($) to the start, so my data displays as $109, instead of just 109. I tried just doing '$' + SUM, but of course that didn't work. Is there a way around this?

推荐答案

像许多语言一样，加号是T-SQL中的重载运算符 - 或字符串连接。当操作中涉及的任何类型都是数字时，优先级将转换为加法。当然，你不能以有意义的方式添加字符串和数字，所以你会得到关于转换的错误。为了将值连接成字符串，必须告诉SQL Server它们都是字符串。一种方法是使用 CONVERT ：

Like in many languages, the plus sign is an overloaded operator in T-SQL - addition or string concatenation. When any of the types involved in the operation are numeric, precedence goes to addition. Of course you can't add strings and numbers in a meaningful way, so you get an error about conversion. In order to concatenate the values as strings, you must tell SQL Server that they're all strings. One way to do this is using CONVERT:

..., '$' + CONVERT(VARCHAR(12), SUM(CAST(price AS INT))) AS Price FROM ...

请注意，在Denali中，您将能够通过使用新的 CONCAT 函数，它将所有类型视为字符串，甚至将 NULL 值作为空字符串。

Note that in Denali you will be able to avoid the converts by using the new CONCAT function, which treats all types as strings and even treats NULL values as empty strings.

这篇关于用数值连接'$'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！