在 SQL Server 2008 中用前导零填充字符串，使其长度为 3 个字符 [英] Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

问题描述

在 SQL Server 2008 R2 中首次创建时，我有一个最长为 3 个字符的字符串.

我想用前导零填充它，所以如果它的原始值是1"，那么新值就是001".或者，如果其原始值是23"，则新值是023".或者如果它的原始值是'124'，那么新值与原始值相同.

我使用的是 SQL Server 2008 R2.我将如何使用 T-SQL 做到这一点?

解决方案

如果该字段已经是字符串，这将起作用

 SELECT RIGHT('000'+ISNULL(field,''),3)

如果您希望空值显示为000"

它可能是一个整数——那么你会想要

 SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

<块引用>

根据问题的要求，此答案仅在长度 <= 3 时有效，如果您想要更大的东西，则需要将字符串常量和两个整数常量更改为所需的宽度.例如 '0000' 和 VARCHAR(4)),4

I have a string that is up to 3 characters long when it's first created in SQL Server 2008 R2.

I would like to pad it with leading zeros, so if its original value was '1' then the new value would be '001'. Or if its original value was '23' the new value is '023'. Or if its original value is '124' then new value is the same as original value.

I am using SQL Server 2008 R2. How would I do this using T-SQL?

解决方案

If the field is already a string, this will work

 SELECT RIGHT('000'+ISNULL(field,''),3)

If you want nulls to show as '000'

It might be an integer -- then you would want

 SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

As required by the question this answer only works if the length <= 3, if you want something larger you need to change the string constant and the two integer constants to the width needed. eg '0000' and VARCHAR(4)),4

这篇关于在 SQL Server 2008 中用前导零填充字符串，使其长度为 3 个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！