In this SQL Server tutorial, I will show you how to use the SQL Server STR function, where you will understand how to convert the number to a string.
You will learn about how the STR() function can be useful in various situations. Then syntax of the STR() function, as well as how to check the datatype of the value after converting the number to string.
Finally, you will use the STR() function to convert the number to a string based on specific length, and also how to include the decimal places while the number is being converted to a string.
SQL Server STR Function
The STR() function in SQL Server converts the given float number into character data (or string). There are various situations or scenarios where the STR() function can be very useful.
- You can format the output of your query, for example, when you need to show or output the numeric value in a specific format, use the STR() function.
- Sometimes you need to convert the numeric value to a string to filter the data based on that converted string value. Here using the STR() function will be helpful.
- When you want to concatenate the numeric values with a string value, but you can’t do that, first, convert the numeric value to a string and then concatenate with other string values. In this case, you can use the STR() function for conversion.
- If you have a numeric value and want to output or display that numeric value with the fix-width format for readability purposes, then here you can use the STR() function.
There are many more situations where you can use the STR() function based on the requirements.
The syntax is given below.
STR ( float_expr [ , len [ , decimal ] ] )Where,
- STR(): It is the function itself for converting the number to a string.
- float_exp: It is the float number (numeric value with decimal point) that is converted to character data or string.
- len: It represents the length of the output string with decimal places, signs, digits, and spaces. By default, the length is 10.
- decimal: It is the number of decimal places you want to include with the output string. By default, it is 16.
The STR() function returns the string of type varchar. Let’s see with a simple example, where convert the float value 453.99 to string using the below query.
SELECT STR(453.99) AS StringValue;
The above query returns the string value 454 of the numeric values 453.99, here also notice that the STR() function rounded the numeric values.
If you are not sure about whether the value is converted to string (varchar) or not, then use the below query to know the datatype of the returned value.
SELECT SQL_VARIANT_PROPERTY(STR(453.99), 'BaseType') AS DataType;
From the above output, the data type of the value that is returned by the STR() function is VARCHAR (string).
The SQL_VARIANT_PROPERTY() is a function that returns the datatype of the specified value. So whenever you need to verify the data type of the value use this function.
This is how you can convert the float number to a string and check the data type of the converted value.
Next, let’s how to truncate the number to a specific length.
Using SQL Server STR Function with Specified Length
As you know from the syntax of the STR() function which accepts the length parameter to specify the length of the resultant string. In other words, if you want to increase the length of the converted string, then you can use the second parameter of STR() for that.
For example, you have a number like 4539 and want to convert this number to a string with a total of 6 characters long. For that use the below query.
SELECT STR(4539,6);
The above output of the query result converted the given number 4539 to a string with a total length of 6 characters. But here STR() function left padded the resulting string with 2 spaces that you can see in the output.
The length of the number (4539) is 4 characters long, but as specified within the STR(…, 6) function after conversion it should be 6 characters long in length. To increase the length of the converted string, the STR() function added 2 spaces at the being of that string.
Now let’s see ‘What happens if you provide a smaller length than the length of the specified number?’.
For example, let’s say you have the number 82745 and you want 4 characters in length after conversion. So use the below query for that.
SELECT STR(82745,4);
As you can see from the above output of the query, it returns the 4 asterisk (*) in case of a smaller length than the specified number. So remember when the length of the specified expression is more than the specified length, then STR() returns a string of asterisks.
- But why the result string is an asterisk, think that you have provided the number which is 5 digits (in the context of a string 5 characters) long, and within the string specified that after conversion the length of the string must be 4 characters long.
- So here you are allocating spaces that are too small to display the full number, so it indicates that the result string is truncated. This is how the STR() function behaves in this kind of circumstance.
This is how to convert the given number to a string based on the specified length using the SQL Server STR function.
Using SQL Server STR Function with Decimal Places
When you pass the third parameter (which is the decimal) to the STR() function, this function includes the decimal number to the right of the number being converted to a string.
For example, if you have a number such as 9827 and you want to add the 2 decimal places based on the specified by the decimal parameter. Then use the below query.
SELECT STR(9827,4,2);
When you execute the above query, you get the same value which string form of the number 9827, and you wanted to include two decimal places, but the output is not the same as expected, although provided the correct parameter value to STR() function such as length should be 4 and should include the 2 decimal places.
Here just focus on the length that you have specified which is 4, now increase the length by 1 and execute the query again.
SELECT STR(9827,5,2);
As you can see in the above output, the converted number still contains the same number but there are no decimal places. But it increases the length of the resultant string by 1 that you can see which is one space at the being of the string.
Now again, I want to you increase the specified length by 1 and execute the query.
SELECT STR(9827,6,2);
Now this time it shows only the one decimal place when you increased the length by 1 from 5 to 6. Again increase the length by 1 and execute the query.
Well now, it includes the two decimal values after the decimal point when you have specified the length equal to 7. As you can see, increasing the length value one by one includes the decimal points to the given number.
So here you need to understand when you want to include the decimal places in the result string, then specify the length equal to the actual length of the provided value + 1 + the number of decimal places.
So if you have a value (2749) of length 4 and you want to include the 3 decimal places, then the actual length of the value is 4, plus 1 and the 3 decimal places, so the total length is 8. For example, use the below query.
SELECT STR(2749,8,3);
After running the above query, it included the 3 decimal places to the number 2749 and converted to string 2749.000. This is how to include the decimal places using the SQL Server STR function.
Conclusion
In this SQL Server tutorial, you learned how to transform the given numeric value to a string using the STR() function in SQL Server. Also how to increase the size of the converted string by specifying the length as well as including the decimal places to the converted string.
You may like to read:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.