SQL中格式化Sortcode字段如何保留前导零的技术问询

Ah, I get it—your problem comes down to how FORMAT() treats numeric values. When you pass 012345 to it, it sees a number, not a string, so those leading zeros get dropped immediately (since numbers don’t care about leading zeros). Let’s fix this with a couple of solid approaches that’ll keep those leading zeros intact:

Approach 1: Use STUFF() for direct string manipulation #

This is my go-to for this kind of formatting because it’s straightforward and works with both string and numeric fields (as long as we cast to string first). It lets you insert dashes exactly where you need them without losing any leading zeros:

SELECT STUFF(STUFF(CAST(CODE AS VARCHAR(6)), 3, 0, '-'), 6, 0, '-') FROM TABLE_NAME

Here’s what each part does:

• CAST(CODE AS VARCHAR(6)) converts your value to a 6-character string, preserving any leading zeros that were present.
• The first STUFF inserts a dash right after the 2nd character (position 3).
• The second STUFF inserts another dash after the 4th character (position 6, since we already added one dash earlier).

Approach 2: Fix the input for FORMAT() (if you prefer sticking with it) #

If you really want to keep using FORMAT(), you just need to make sure you’re passing it a fixed-length string with leading zeros instead of a number. Here’s how to do that:

-- For SQL Server (ensures we have a 6-character string first)
SELECT STRING_FORMAT(RIGHT('000000' + CAST(CODE AS VARCHAR(6)), 6), '##-##-##') FROM TABLE_NAME

The RIGHT('000000' + CAST(CODE AS VARCHAR(6)), 6) part is key here—it pads the front of your value with zeros to make sure it’s always 6 characters long before formatting.

A quick note: If your CODE column is already a string type (not numeric), you can skip the CAST(CODE AS VARCHAR(6)) step and just use the column directly in either of these methods.

内容的提问来源于stack exchange，提问作者James Knight