SQL - STR() Function

---

---

The SQL STR() function is used to convert a numeric value to a character value.

It accepts three parameters float_exp, length, and decimal, and returns a number as a string. The return type of this function is varchar. Both length and decimal parameters are optional for this function, and their default values are 10, and 0 simultaneously.

Note − If any argument passes as NULL value to the function, this will return a NULL value in the result.

Syntax

Following is the syntax of the SQL STR() function −

STR(float_expression,length ,decimal)  

Parameters

• float_expression − It Is an expression of approximate numeric or float data type with a decimal point.

• length − It is the total length of the returned string(optional).

• decimal − It is the number of places to the right of the decimal point(optional).

Return value

This function returns a number as string.

Example

In the following example,we are using the SQL STR() function to convert the number 202.32 into characters.

SELECT STR(202) AS Result;

Output

Following is the output of the above query −

+-----------+
| Result    |
+-----------+
| 202       |
+-----------+

Example

If we pass a numeric value and length as an argument to this function, it returns a number as a character followed by the given length.

Following is another example of the STR() function, using it we are trying to convert a numeric value of 5653.34422 to a character with a length value is 8.

SELECT STR(5653.34422 , 8) AS Result;

Output

On executing the above program, it will produce the following output −

+-------------+
| Result      |
+-------------+
| 5653        |
+-------------+

Example

If we pass a numeric value, length, and decimal value as an argument to the function, the STR() function returns a number as a character followed by the given length and decimal value.

In this example, we are passing a length with a value of 5, and a decimal with a value of 10 as a parameter to the STR() function to retrieve the character value of the given number 134.45676.

SELECT STR(134.45676, 5, 10) AS Result;

Output

The above statement produces the following output −

+-----------+
| Result    |
+-----------+
| 134.5     |
+-----------+

Example

You can also pass a table column as an argument to the STR() function to retrieve a number as a string. Assume we have created a table with the name Customers using the CREATE statement as follows −

CREATE TABLE CUSTOMERS(    
ID INT NOT NULL,    
FIRST_NAME VARCHAR (20),
LAST_NAME VARCHAR(20),
AGE INT NOT NULL,    
ADDRESS CHAR (25) ,    
SALARY DECIMAL (18, 2));

Now let's insert some records into the customers table using the INSERT statement as follows:−

INSERT INTO CUSTOMERS VALUES (1, 'Ramesh','KUMAR', 32, 'Ahmedabad', 2000.00 ); 
INSERT INTO CUSTOMERS VALUES (2, 'Khilan','Verma', 25, 'Delhi', 1500.00 ); 
INSERT INTO CUSTOMERS VALUES (3, 'kaushik','Gupta', 23, 'Kota', 2000.00 ); 
INSERT INTO CUSTOMERS VALUES (4, 'Chaitali','Pal', 25, 'Mumbai', 6500.00 );

The Following SQL query convert the number as string of the content of the Salary column in the Customers table −

SELECT ID, FIRST_NAME, STR(SALARY) AS RESULT FROM CUSTOMERS;

Output

Following is the output of the above query −

+----+------------+---------------+
| ID | FIRST_NAME | RESULT        |
+----+------------+---------------+
|  1 | Ramesh     | 2000          |
|  2 | Khilan     | 1500          |
|  3 | kaushik    | 2000          |
|  4 | Chaitali   | 6500          |
+----+------------+---------------+