How would you format a big number in SQL Server with the appropriate symbol depending on the number? Such as
12,100,000,000 = 12.1bn
800,000,000 = 800mn
2,100,000,000,000 = 2.1tn
Format(num_value,'N3') puts it in commas, but I need it to be more like decimal examples above. Is there a way to do this without dividing dynamically?
First up, the numbers in your examples indicate that you can't keep them in a column with numeric data type (like bigint) they are too big. Therefore you can't use numeric formatting capabilities offered by the format function. I am guessing that you keep them in varchar columns as text. There is no built-in formatting capability in SQL Server to format these the way you want. You can write a scalar function (beware of performance implications of these when used in queries calling it many times (millions of times) Even the Format function, when used for numeric data, will not be aware of units like tonnes, kg, etc.
