I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the STR() function today.
The STR() function takes a floating point number [F] to be converted to a string, a integer number [L] representing the total length, and a integer number [D] representing the number of decimal digits as input.
It returns a formatted string representing the floating point number.
The length [L] includes decimal point, sign, numbers and spaces. The decimal digits [D] represents the number of digits right of the decimal point.
The example below takes a floating point number and rounds it to two decimal places.
|
1 2 |
-- Format as xxx.xx select str(123.456, 6, 2) as str_number; |
|
1 2 3 4 5 |
output: str_number ---------- 123.46 |
One thing to worry about when using TSQL functions is how will it react with out of range values. In the example below, a negative values are used for total length and number of decimal places.
Both examples return a NULL string as output.
|
1 2 3 4 5 |
-- Invalid total length returns NULL select str(123.456, -6, 2) as str_number; -- Invalid decimal digits returns NULL select str(123.456, 6, -2) as str_number; |
|
1 2 3 4 5 |
output: str_number ---------- NULL |
Another thing to always worry about when using TSQL functions is how will it react with a EMPTY strings or NULL values? I was surprised to find out that a empty string returns zero instead of the expected NULL value.
|
1 2 3 4 5 |
-- Empty string returns 0.00 select str('', 6, 2) as str_number; -- Null returns Null select str(NULL, 6, 2) as str_number; |
|
1 2 3 4 5 6 7 8 9 |
output: str_number ---------- 0.00 str_number ---------- NULL |
To recap, the STR() function was the only system supplied function for formatting numbers before the FORMAT() function was introduced in SQL Server 2012.
I will be exploring the STUFF() function next time. Is it Thanksgiving Day already?
