- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Databases
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
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 | +----+------------+-------------+-----------+
To Continue Learning Please Login
Login with Google