In the database, there is a number (rawresult) and a number format (format) in a single row. I want to format the rawresult to how format says it should be with a single query. This is a database that already exists and we can't modify it.
Example 1: (rawresult = 1.254; format = 0.0), output should be 1.3
Example 2: (rawresult = 1.254; format = 0.00000), output should be 1.25400
Example 3: (rawresult = 10.254; format = 0.000), output should be 10.254
The format field can be null, 0, 0.0, have 6 decimal places, or anything in between. I have no idea how to go about doing this with a single SQL query. Sometimes, format will be null, but that part I can actually handle. Basically, I need to round it if format has less decimal places than rawresult, or add trailing 0s if format requires more decimal places than rawresult has.
Thanks!
A possible approach is the FORMAT() function, but the format must contain a valid .NET Framework format string:
SELECT FORMAT([rawresult], [format]) AS [result]
FROM (VALUES
(1.254, NULL),
(1.254, '0.0'),
(1.254, '0.00000'),
(10.254, '0.000')
) v ([rawresult], [format])
Result:
result
-------
1.254
1.3
1.25400
10.254