SQL - STUFF() Function

---

---

The SQL STUFF() function is used to insert a string into another string.

It accepts four parameters char_exp, start, length, and replace_with_exp, and deletes a specified length of characters in the first string at the starting position and then inserts the second string into the first string at the start position.

• If the start parameter value is negative, zero, or longer than the first expression, a null string is returned.

• If the length parameter value is negative, a null string will be returned, and if the length is longer than the first expression, deletion occurs up to the last character in the second expression.

• If the length is zero, insertion occurs at the start location, and no characters are deleted from the first expression.

Syntax

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

STUFF ( character_expression , start , length , replace_with_expression )

Parameters

• character_expression − It is an expression of character data(first string).

• start − It is an integer value that specifies the position to start deletion and insertion.

• length − It is an integer value that specifies the number of characters to be deleted.

• replace_with_expression − It is an expression of characters data (second string.)

Return value

This function returns a new string by inserting the second expression at the specified deleted place.

Example

In the following example,we are using the SQL STUFF() function to insert the String second ‘world’ into the first String ‘hello’ at the specified starting position 2, and length 3.

SELECT STUFF('hello', 2, 3, 'world') AS NEW_STR;

Output

The above SQL query generates the following output −

+----------+
| NEW_STR  |
+----------+
| hworldo  |
+----------+

Example

If the start argument value is passed as NULL OR 0(zero) to the function, it reruns a NULL string.

In this example, we are using the STUFF() function to insert the second String ‘defgh’ into the first String ‘abc’ at the specified starting position NULL or 0 and length 2.

SELECT STUFF('abc', NULL, 2, 'defgh') AS NEW_STR;
SELECT STUFF('abc', 0, 2, 'defgh') AS NEW_STR;

Output

Following is the output of the above query −

+----------+
| NEW_STR  |
+----------+
| NULL     |
+----------+

Example

If the length argument value is passed as -1 (negative value) to the function, the STUFF() function returns a NULL string.

In this program, we are using the SQL STUFF() function to insert the String ‘point’ into the String ‘Tutorials’ at the specified starting position 2, and length -1.

SELECT STUFF('Tutorials', 2, -1, 'point') AS NEW_STR;

Output

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

+----------+
| NEW_STR  |
+----------+
| NULL     |
+----------+

Example

If the length argument value is passed as zero, insertion occurs at the start position, and no characters are deleted from the first expression.

SELECT STUFF('Tutorials', 2, 0, 'point') AS NEW_STR;

Output

After executing the above statement, it produces the following output −

+----------------+
| NEW_STR        |
+----------------+
| Tpointutorials |
+----------------+

Example

If the length argument value is longer than the first expression, deletion occurs up to the last character in the second expression.

In the following example,we are using the SQL STUFF() function to insert the second String ‘point’ into the first String ‘Tutorials’ at the specified starting position 2, and the length whose value is longer than the first String 15.

SELECT STUFF('Tutorials', 2, 15, 'point') AS NEW_STR;

Output

Following is the output of the above query −

+----------------+
| NEW_STR        |
+----------------+
| Tpoint         |
+----------------+

Example

You can also pass the column's name as an argument to the SQL STUFF() function to insert the one-column values into another column. 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),s
AGE INT NOT NULL,    
ADDRESS CHAR (25) ,    
SALARY DECIMAL (18, 2));

Let’s insert some records into the Customers table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES (1, 'Rohan','Verma', 33, 'Hyderbad', 2100.00 ); 
INSERT INTO CUSTOMERS VALUES (2, 'Kamlesh','Kumar', 30, 'Lucknow', 2500.00 ); 
INSERT INTO CUSTOMERS VALUES (3, 'Seeta','Sharma', 23, 'Delhi', 3150.00 );

The following SQL query inserts one-column LAST_NAME value into the another column FIRST_NAME into the Customer table −

SELECT ID, FIRST_NAME,LAST_NAME, STUFF(FIRST_NAME, 2, 4, LAST_NAME) AS NEW_NAME FROM CUSTOMERS;

Output

Following is the output of the above query −

+----+------------+-------------+-----------+
| ID | FIRST_NAME | LAST_NAME   |NEW_NAME   |           
+----+------------+-------------+-----------+
|  1 | Rohan      | Verma       |  RVerma   |
|  2 | Kamlesh    | Kumar       |  KKumarsh |
|  3 | Seeta      | Sharma      |  SSharma  |
+----+------------+-------------+-----------+