Problem
If you’ve ever been tasked to work with strings in Microsoft SQL Server, chances are you’ve become familiar with the different string functions that can help make the process easier. Two of the more commonly used functions for updating string values in a table are STUFF() and REPLACE(). But what about .WRITE(), a relatively lesser-known function?
Although the .WRITE() function has been around since SQL Server 2005, it’s still not commonly discussed or used in SQL databases.
Solution
In this tutorial, we’ll explore the differences between these three string manipulation T-SQL functions (STUFF, REPLACE, WRITE) in SQL Server and how they can be used to achieve several tasks. Using a self-made test table, we will walk through the descriptions and examples and explain when and why you should use one over the other based on the use case.
Prerequisites
To complete the examples in this article, we will need to build a simple test table to work with. All of the subsequent scripts will work on this test table.
Script for Building the Test Table
IF OBJECT_ID('CheckWrite') IS NOT NULL
DROP TABLE CheckWrite;
GO
CREATE TABLE CheckWrite(
empID INT
, empName VARCHAR(50)
, directions VARCHAR(MAX)
);
GO
Script for Adding Generic Data to the Test Table
INSERT INTO CheckWrite
VALUES
(101, 'Daisy', 'North on Pike, South on Lee'),
(102, 'Brianna', 'North on Pike, South on Lee'),
(103, 'Sethu', 'North on Pike, South on Lee'),
(104, 'Crystal', 'North on Pike, South on Lee')
;
GO
SQL STUFF Function
The SQL Server STUFF() function is a string manipulation function that inserts a string into another string, starting at a specified position. The STUFF() function removes a specified number of characters from the original string and then inserts the new string.
Basic Syntax:
STUFF(string, start, length, replaceWith)
Breaking down the “Basic Syntax” in the code block above:
- “string” is the string that you want to modify
- “start” is the position of the first character that you want to delete
- “length” is the number of following characters that you want to delete
- “replaceWith” is the string that you want to insert in place of the deleted characters
Let’s practice this with a simple string that we generate.
SELECT STUFF('Hello World', 7, 5, 'Everyone');
In our sample above, we start at position 7 of our string “Hello World” and replace the next five characters with the new string “Everyone.”
Here’s a number line that shows what position each character is in for your reference:
Results:
Now, let’s apply this same theory to a string value in a SQL table. Let’s first look at the “directions” column of our table that we created in the “prerequisites” section at the beginning of this article. Below is a number line for each character position.
In this example, we will replace the string value “South” with the string value “East.” If you have not done so or need to refresh your test table, run the scripts in the “Prerequisites” section at the top of this article. This will build/rebuild our test table and populate it with some generic data.
Looking at our number line above, we see that the string value “South” starts at position 16 and contains five characters. These two numbers will be the parameters for our STUFF() function.
UPDATE CheckWrite
SET directions = STUFF(directions, 16, 5, 'East')
WHERE empID = 101;
GO
If we run a SQL query to return all the rows in the table with a SELECT statement, you will see that the string value “South” has been replaced with the string value “East,” but only on the row with an “empid” value of 101, no other values have changed in that row or any subsequent rows.
SELECT * FROM CheckWrite;
GO
Results:
SQL REPLACE Function
The SQL REPLACE() function is the most versatile of the three. It can replace a single character, multiple characters, or all occurrences of a character in a string. It can also insert new characters into a string at a specific position.
It is important to note that while most people say that SQL is not case-sensitive, for the most part, it is not. However, with the REPLACE() function, it is only in respect of the string values. The SQL commands remain case-insensitive.
Here are a couple of samples to demonstrate the case sensitivity within the REPLACE() function and to better understand how the REPLACE() function works.
In this first example, we will replace each instance of ‘T’ with a lowercase ‘q’.
SELECT REPLACE('SQL Tutorial', 'T', 'Q');
Results:
You will notice that the upper and lower case ‘T’s were replaced with an upper case ‘Q’ in our result set.
Following the same pattern, we replace the upper and lower case “T” with the lower case “q” in the SQL query below.
SELECT REPLACE('SQL tutorial', 't', 'q');
Results:
You may have also noticed that case sensitivity did not factor in our results for the character we wanted to be replaced. Our statement’s upper or lower case “T” did not affect the results.
Following suit, you can replace an entire string using the REPLACE() function with this SELECT statement.
SELECT REPLACE('SQL Tutorial', 'SQL Tutorial', 'MSSQLTIPS Article');
Results:
SQL .WRITE Function
The .WRITE() function is a little different from the STUFF() and REPLACE() functions. It still places a new string value in a table, but it has a few idiosyncrasies:
- Unlike STUFF(), the .WRITE() function only works when a column’s data type size is specified as MAX.
- The .WRITE() function is zero-based. (more on this later)
- The .WRITE() function must be proceeded with a decimal point in the name [.WRITE()].
First, let’s discuss item number 1 from the list above. Using the examples that we have been working with, the .WRITE() function will not work on a column with a data type of varchar(50), such as our “empName” column, or any other data types with a specified numeric value. It must have a data type of varchar(max). If you try to use the .WRITE() function on anything other than a MAX data type value, you will receive an error like this one.
Next, let’s discuss that “zero-based” comment in item number two from the above list.
Referring to the number line in the previous sections, you would have noticed that the counting begins with 1 as the first position. However, the .WRITE() function starts with 0 (zero) in the first position. Several programming languages use this method, Python, for example. Below is the number line that we will be using for the remaining code samples of this article. Notice that our number line starts with the number 0 (zero), as we just mentioned.
.WRITE() Function Examples
The .WRITE() function writes a character to a given position in a string. The first argument is the character(s) that you want to write to a string value, and the second is the position where you want it to begin. The third argument represents how many character spaces the string will consume.
Finally, unlike the STUFF() and REPLACE() functions, the .WRITE() function must be preceded with a decimal point.
Let’s take a look at some samples of using the SQL .WRITE() function.
Using our original table (rebuild it if needed), we will replace the word “South” with the word “Left, ” much like we did in the STUFF() section above.
UPDATE CheckWrite
SET directions .WRITE('left', 15, 5)
WHERE empID = 101;
GO
Results:
Like the SQL STUFF() function, you can replace the trailing value with 0 (zero) if you want to insert a new string into your existing string. In this example, we will insert the string value “left or “in front of the string value “South.” We are not replacing “South,” but instead appending it with this UPDATE statement.
UPDATE CheckWrite
SET directions .WRITE('left or ', 15, 0)
WHERE empID = 101;
GO
Results:
So, why would I use the SQL .WRITE() function when the STUFF() function performs similarly, only without the MAX restriction? In a nutshell, while there is little or no performance difference in small examples like this article, the .WRITE() function will work on large amounts of string values, where the STUFF() function will not.
Wrap Up
The SQL STUFF() function is the best choice when inserting or deleting characters within a string. For example, if you need to insert commas between words in a list, the SQL STUFF() function is the way to go. The SQL REPLACE() function is best used when updating an existing string. For example, if you need to replace all occurrences of "Sample" with "Production,” REPLACE is the method you would use. If you need to replace large blocks of one or more stings, the SQL .WRITE() function would be the best choice.
Next Steps
- Play around with these options and try creating your own examples.
- Check out these tips:
