SQL STR() String Function

The SQL STR() function converts a numeric value into a string (character data type), allowing for formatting and further manipulation of numeric data as text.

The SQL STR() function is useful when you need to apply string operations on numbers, format numbers for display, or handle concatenations involving numeric and text data.

The STR() function is supported in SQL Server and other SQL databases.

In this tutorial, we will go through SQL STR() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL STR() Function

The basic syntax of the SQL STR() function is:

</>
Copy
STR(number, length, decimal);

Each part of this syntax has a specific purpose:

  • number: The numeric value you want to convert to a string.
  • length (optional): The total length of the returned string, including the decimal point and any negative sign. The default is usually the actual length of the number.
  • decimal (optional): The number of decimal places to include in the result. If omitted, no decimals are included.

The STR() function returns the specified numeric value as a right-aligned character string, with optional length and decimal precision.

Setup for Examples: Creating the Database and Table

We’ll create a sample sales table with fields amount and discount_rate. Follow these steps to set up the data for the STR() function examples.

1. First, create a new database called store:

</>
Copy
CREATE DATABASE store;

2. Select the store database to work with:

</>
Copy
USE store;

3. Create a table named sales with the fields sale_id, amount, and discount_rate:

</>
Copy
CREATE TABLE sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(10, 2),
    discount_rate DECIMAL(5, 2)
);

4. Insert sample data into the sales table to use with the STR() function examples:

</>
Copy
INSERT INTO sales (amount, discount_rate)
VALUES
(250.75, 5.5),
(1200.00, 10.0),
(500.5, 7.75),
(750.99, 12.5),
(1500.20, 15.0);

With this setup complete, you can run the STR() function examples to test and view results in the sales table.

Examples: Using STR() in SQL Queries

We’ll go through examples demonstrating the STR() function in SQL, using sample data from a sales table with fields amount and discount_rate.

1. Converting a Numeric Value to a String

To convert a numeric value in the amount column to a string:

</>
Copy
SELECT amount, STR(amount) AS amount_string
FROM sales;

This query converts each amount value to a string without specifying a length or decimal places, providing flexibility for further string operations.

2. Formatting with Specified Length and Decimal Places

To format the amount column as a string with a total length of 10 characters and 2 decimal places:

</>
Copy
SELECT amount, STR(amount, 10, 2) AS formatted_amount
FROM sales;

This query formats each amount as a string with two decimal places, providing a consistent display width of 10 characters, useful for alignment in reports.

3. Using STR() to Prepare Data for Concatenation

To concatenate a discount_rate numeric value with a percentage symbol:

</>
Copy
SELECT discount_rate, 
       CONCAT(STR(discount_rate, 5, 2), '%') AS discount_display
FROM sales;

This query converts the discount_rate to a string with two decimal places and adds a percentage symbol for display purposes.

4. Combining STR() with Other String Functions

To convert amount to a string and add padding with the SPACE() function for formatted output:

</>
Copy
SELECT amount, 
       CONCAT(SPACE(5), STR(amount, 8, 2)) AS padded_amount
FROM sales;

This query converts amount to a string and pads it with spaces, useful for creating aligned and readable output in reports.

FAQs for SQL STR()

1. What does the SQL STR() function do?

The STR() function converts a numeric value to a string, allowing for further formatting and string manipulation.

2. Can STR() handle NULL values?

If the input number is NULL, STR() returns NULL.

3. Is STR() supported in all SQL databases?

The STR() function is supported in SQL Server, but other databases may use similar functions or type conversion methods.

4. Can STR() be combined with other string functions?

Yes, STR() can be combined with functions like CONCAT() and SPACE() for custom formatting and alignment.

5. What is the default length and decimal place for STR()?

If length and decimal places are not specified, STR() defaults to the actual length of the number with no decimal places.