如何将int转换为带有前导零的char? [英] How to convert int to char with leading zeros?

问题描述

我需要将 int 数据字段转换为带前导零的 nvarchar

I need to convert int datafield to nvarchar with leading zeros

示例:

1 转换为001"

867 转换为 '000867' 等

867 convert to '000867', etc.

谢谢.

这是我 4 小时后的回复......

This is my response 4 Hours later ...

我测试了这个 T-SQL 脚本，对我来说效果很好！

I tested this T-SQL Script and work fine for me !

DECLARE @number1 INT, @number2 INT

SET @number1 = 1

SET @number2 = 867

SELECT RIGHT('000' + CAST(@number1 AS NCHAR(3)), 3 ) AS NUMBER_CONVERTED

SELECT RIGHT('000000' + CAST(@number2 AS NCHAR(6)), 6 ) AS NUMBER_CONVERTED

<小时>

我创建了这个用户函数

---

I created this user function

T-SQL 代码:

CREATE FUNCTION CIntToChar(@intVal BIGINT, @intLen Int) RETURNS nvarchar(20)
AS
BEGIN

    -- BIGINT = 2^63-1 (9,223,372,036,854,775,807) Max size number

    -- @intlen contains the string size to return
    IF @intlen > 20
       SET @intlen = 20

    RETURN REPLICATE('0',@intLen-LEN(RTRIM(CONVERT(nvarchar(20),@intVal)))) 
        + CONVERT(nvarchar(20),@intVal)

END

示例:

SELECT dbo.CIntToChar( 867, 6 ) AS COD_ID

SELECT dbo.CIntToChar( 867, 6 ) AS COD_ID

输出

000867

推荐答案

试试这个:select right('00000' + cast(Your_Field as varchar(5)), 5)

它将得到 5 位数字的结果，例如:00001,...., 01234

It will get the result in 5 digits, ex: 00001,...., 01234

这篇关于如何将int转换为带有前导零的char?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！