SQL Server Format() 函数

发布于 11-30 01:11 字数 772 浏览 1 评论 0原文

我在 MS Access 数据库中有一个当前查询,该查询使用 Format() 函数将数字(长整数)转换为字符串。

该表已被重新设计并移至 SQL Server。我需要转换的字段不是货币字段,我需要删除句点之后的所有内容并将其转换为具有一定数量字符的字符串。

示例数据:

Amount
228.00000
1896.00000
6411.00000
317.00000
4830.00000
898.00000
1778.00000
1308.00000
45139.00000
424.00000

当前的 MS Access 查询:

SELECT Format(CallAmt],"000000000000000") AS Amount
FROM Fed

新查询:

SELECT Convert(varchar(15), FedAmount)
FROM Fed.SM_T_Call

我希望我的最终产品如下所示:

Amount
000000000000228
000000000001896
000000000006411
000000000000317
000000000004830
000000000000898
000000000001778
000000000001308
000000000045139
000000000000424

我不知道如何删除句点后的所有内容并向结果添加额外的零。有人可以提供任何帮助吗?

I have a current query in an MS Access database that uses the Format() function to convert a number (long integer) to a string.

The table has been redesigned and moved to SQL server. The field I need to convert is not a money field that I need to drop everything after the period and convert it to a string with a certain number of characters.

Sample Data:

Amount
228.00000
1896.00000
6411.00000
317.00000
4830.00000
898.00000
1778.00000
1308.00000
45139.00000
424.00000

Current MS Access query:

SELECT Format(CallAmt],"000000000000000") AS Amount
FROM Fed

New query:

SELECT Convert(varchar(15), FedAmount)
FROM Fed.SM_T_Call

I would like my final Product to look like this:

Amount
000000000000228
000000000001896
000000000006411
000000000000317
000000000004830
000000000000898
000000000001778
000000000001308
000000000045139
000000000000424

I don't know how to drop everything after the period and add the extra zeros to my result. Can anyone offer any assistance?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

伊面2024-12-07 01:11:38
select Cast(Cast(fedamount as int), as varchar(15))
from Fed.SM_T_Call

可能真的有效吗?

select Cast(Cast(fedamount as int), as varchar(15))
from Fed.SM_T_Call

Might actually work?

断桥再见2024-12-07 01:11:38

@gbn 的答案对于 SQL Server 来说是一个很好的答案,它使用专有函数 RIGHT()REPLICATE()

请注意,您已经必须移植一次,以下版本可能是更可移植的版本,因为它使用标准 SQL 函数。请注意,标准 SQL 中的字符串连接运算符是 ||,而 SQL Server 使用 + 进行相同操作(MS 仍然声称它是兼容的,请看图)。然而,所提出的代码在可移植性方面仍然得分很高,因为将 `|| 转换为 + 是微不足道的:

REVERSE(CAST(REVERSE(CAST(CAST(Amount AS INTEGER) AS VARCHAR(15))) 
   + '000000000000000' AS CHAR(15)))

@gbn'd answer is a good one for SQL Server and uses proprietary functions RIGHT() and REPLICATE().

Noting that you've already had to port this once, the following may be a more portable version because it uses Standard SQL functions. Note the string concatenation operator in Standard SQL is ||, whereas SQL Server uses + for the same (and MS still claims it is compliant, go figure). However, the proposed code still scores highly on the portability scale because transforming `|| to + is trivial:

REVERSE(CAST(REVERSE(CAST(CAST(Amount AS INTEGER) AS VARCHAR(15))) 
   + '000000000000000' AS CHAR(15)))
携余温的黄昏2024-12-07 01:11:37

您可以使用:

  • CAST 为小数以获得固定的小数位数
  • 使用 STR 右对齐
  • 使用 RIGHT('000000000000000' + CAST(MyNumber AS varchar(15)), 15) 具有前导零

或者以上所有:但我们不知道您想要什么输出...

编辑,更新后

RIGHT('000000000000000' + CAST(CAST(Amount AS int) AS varchar(15)), 15)

编辑,2011 年 10 月

SQL Server 2012 (Denali) 将有格式化功能。

编辑,在 onedaywhen 发表评论后

DECLARE @len tinyint;
SET @len = 15;
RIGHT(REPLICATE('0', @len) + CAST(CAST(Amount AS int) AS varchar(255)), @len)

You can use:

  • CAST to decimal to have a fixed number of decimal places
  • Use STR to right justify
  • Use RIGHT('000000000000000' + CAST(MyNumber AS varchar(15)), 15) to have leading zeros

Or all of the above: but we don't know what output you want...

Edit, after update

RIGHT('000000000000000' + CAST(CAST(Amount AS int) AS varchar(15)), 15)

Edit, October 2011

SQL Server 2012 (Denali) will have Format function.

Edit, after comment by onedaywhen

DECLARE @len tinyint;
SET @len = 15;
RIGHT(REPLICATE('0', @len) + CAST(CAST(Amount AS int) AS varchar(255)), @len)
执妄2024-12-07 01:11:37
SELECT CAST(CAST(FLOOR(FedAmont) AS INT) AS VARCHAR(40)) + '.00'
SELECT CAST(CAST(FLOOR(FedAmont) AS INT) AS VARCHAR(40)) + '.00'
所有深爱都是秘密2024-12-07 01:11:37

您可以使用十进制数据类型:

declare @var decimal(15,6)
set @var = 228564.12345678

然后如果您希望它是 varchar 之后:

select convert(varchar(15),@var)

或者在选择中,它可能如下所示:

select 
   [field1] as OldValue,
   convert(decimal(15,6),[field1]) as NewValue
from 
   myTable

You can use the decimal data type:

declare @var decimal(15,6)
set @var = 228564.12345678

And then if you want it to be a varchar after that:

select convert(varchar(15),@var)

Or in a select, it could look like this:

select 
   [field1] as OldValue,
   convert(decimal(15,6),[field1]) as NewValue
from 
   myTable
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文