SQL数字转字符串：不足3位补前导0，多位保留原数的实现问题求助

Hey there! Let's tackle that formatting issue you're running into. Your current approach works for numbers with 3 digits or fewer, but truncates longer numbers like 1234 to 234—here's why and how to fix it:

What's Wrong with the Original Code? #

Your code has two key issues:

• The RIGHT function syntax is incorrect (SQL Server's RIGHT only takes 2 arguments: the target string and the length to extract).
• Casting @id to varchar(3) truncates numbers longer than 3 digits to just the first 3 characters, so even if you fixed the RIGHT call, you'd still lose digits from longer numbers.

Solution 1: CASE Statement (Works in All SQL Server Versions) #

This approach explicitly checks the length of the number string and applies the formatting logic accordingly:

declare @id int; 
set @id = 13; -- Test with 13, 123, 1234, or 5
select 
    CASE 
        WHEN LEN(CAST(@id AS VARCHAR)) < 3 THEN RIGHT('000' + CAST(@id AS VARCHAR), 3)
        ELSE CAST(@id AS VARCHAR)
    END AS FormattedID;

• For numbers <100 (1-99): Concatenates with '000' then takes the last 3 characters to pad leading zeros (e.g., 13 → '00013' → '013').
• For numbers ≥100: Returns the full number as a string without truncation (e.g., 1234 → '1234').

Solution 2: FORMAT Function (SQL Server 2012+) #

If you're using SQL Server 2012 or later, the FORMAT function makes this even cleaner:

declare @id int; 
set @id = 1234; -- Test any number
select FORMAT(@id, '000') AS FormattedID;

• The format string '000' tells SQL Server to pad with leading zeros for numbers with fewer than 3 digits, and automatically retain all digits for numbers with 3 or more digits (no truncation!).
• Quick examples:
  • 5 → '005'
  • 13 → '013'
  • 123 → '123'
  • 1234 → '1234'

Test It Out #

Both solutions will handle all your cases correctly—pick the one that fits your SQL Server version and coding style!

内容的提问来源于stack exchange，提问作者Sandeep Thomas