PostgreSQL中to_char(g.code,'FM00')在Microsoft SQL Server中的等价实现咨询

Got it, let's break down how to replicate that exact behavior in SQL Server. The PostgreSQL to_char(g.code, 'FM00') formats the numeric value as a 2-digit string with leading zeros (no leading spaces)—so values like 5 become '05', 12 stays '12', and 0 becomes '00'.

Here are two reliable solutions depending on your SQL Server version:

1. For SQL Server 2012 and later (simplest approach) #

Use the FORMAT() function, which natively handles this formatting without extra space padding:

SELECT FORMAT(g.code, '00') AS granularity
FROM your_table g;

This works exactly like the PostgreSQL FM00 pattern: it adds leading zeros to make the string 2 characters long, and there are no leading spaces. Perfect for modern SQL Server environments.

2. For older SQL Server versions (pre-2012, backward-compatible) #

If you're stuck on an older version where FORMAT() isn't available, use string concatenation with RIGHT() to achieve the same result:

SELECT RIGHT('00' + CAST(g.code AS VARCHAR(2)), 2) AS granularity
FROM your_table g;

Here's what this does step by step:

• CAST(g.code AS VARCHAR(2)) converts the numeric value to a string
• '00' + ... prepends two zeros to the string (so 5 becomes '005', 12 becomes '0012')
• RIGHT(..., 2) takes the last two characters of that combined string, giving you the 2-digit formatted value without leading spaces.

Quick behavior verification #

Both methods will match the behavior you're getting from PostgreSQL's FM00 format. Pick the one that fits your SQL Server version!

内容的提问来源于stack exchange，提问作者Saul.C