SQL Server String STR() Function

SQL Server STR() Function

The SQL Server STR() function is used to convert a numeric value to a string. This function is useful for formatting numbers as strings in SQL queries, especially when dealing with concatenation or display purposes.

Syntax

SELECT STR(number, length, decimal_places);

The STR() function takes three arguments:

  • number: The numeric value to be converted to a string.
  • length: The total length of the resulting string, including decimal point, digits, and spaces.
  • decimal_places: The number of decimal places to include in the resulting string.

Example SQL Server STR() Function Queries

Let's look at some examples of SQL Server STR() function queries:

1. Basic STR() Example

SELECT STR(123.456, 6, 2) AS result;

This query converts the number 123.456 to a string with a total length of 6 and 2 decimal places. The result will be:

result
------
123.46

2. STR() with Padding

SELECT STR(78.9, 5, 1) AS result;

This query converts the number 78.9 to a string with a total length of 5 and 1 decimal place, including padding. The result will be:

result
------
 78.9

3. STR() with a Column

SELECT sales_amount, STR(sales_amount, 10, 2) AS formatted_sales_amount
FROM sales;

This query converts the sales_amount column to a string with a total length of 10 and 2 decimal places for each record. The result will show the sales_amount and the formatted version as formatted_sales_amount.

4. STR() with a Variable

DECLARE @amount DECIMAL(10, 2);
SET @amount = 1234.56;
SELECT STR(@amount, 8, 2) AS result;

This query uses a variable to store a numeric value and then converts it to a string. The result will be:

result
---------
 1234.56

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the STR() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    amount DECIMAL(10, 2)
);

In this example, we create a table named example_table with columns for id and amount.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, amount) VALUES (1, 123.45);
INSERT INTO example_table (id, amount) VALUES (2, 6789.01);
INSERT INTO example_table (id, amount) VALUES (3, 23.456);

Here, we insert data into the example_table.

Step 3: Using the STR() Function

This step involves using the STR() function to format the amount column as a string.

SELECT id, amount, STR(amount, 10, 2) AS formatted_amount
FROM example_table;

This query retrieves the id, amount, and the formatted version of the amount column for each row in the example_table. The result will be:

id  amount  formatted_amount
--- ------- ----------------
1   123.45  123.45
2   6789.01 6789.01
3   23.46   23.46

Conclusion

The SQL Server STR() function is a powerful tool for converting numeric values to strings. Understanding how to use the STR() function and its syntax is essential for effective string manipulation and data formatting in SQL Server.