Problem
As a DBA, I have used the SQL Server STUFF() function as well as the SQL Server REPLACE() function. However, I’m having a great deal of difficulty finding anything about the .WRITE() function. I would like to learn more about this function: what it can do, its limitations, and any specifics associated with it.
Solution
First and foremost, we must understand that if you are searching for the “SQL Server .WRITE() Function”, technically, you may be conducting the search wrong. Actually, .WRITE(), although followed by parenthesis like other functions, is more accurately described as a clause. However, searching for the .WRITE() clause yields no more results than when calling it a function. Generally speaking, it is a clause within the “UPDATE” statement. Some may argue that it is a function inside the “UPDATE” statement. Opinions vary on the precise terminology on this subject.
As an active DBA, I have also had difficulty finding anything about the SQL Server .WRITE() function. I first learned about this SQL function some years ago and quickly put it on the back burner simply because I did not have an immediate use for it. I promised myself that this was something I would investigate someday. Well, it’s the future now and time to bring out the SQL Server .WRITE() function, explain what it is, what it does, and how to use it.
In this article, we will look at the differences between that elusive .WRITE() function and its counterparts, STUFF() and REPLACE().
SQL Server .WRITE() Function Overview
The SQL Server .WRITE() function is similar in nature to the STUFF() and REPLACE() functions in the respect that the .WRITE() clause inserts data into one or more tuples in SQL Server. However, there are some limitations, if you will, that separate the functionality of the .WRITE() function from the other STUFF() and REPLACE() options. In the code block below, you’ll see the basic syntax layout for the .WRITE() function followed by a brief explanation of each parameter.
UPDATE tableName
SET columnName .WRITE('expression', @OffsetValue, @LenghtValue)
- The “UPDATE tableName” line is our basic command that tells SQL Server that we want to make changes on a specified table.
- “SET columnName” specifies which column in that table we want to modify.
- The “.WRITE” command indicates that we want to modify one or more values in our selected column. We will need to add a WHERE clause to specify one or more particular rows.
- “expression” represents the text value we want to insert into each tuple of our table.
- @OffsetValue represents the position that we want to start the insert process.
- @LengthValue indicates how many character spaces we want to overwrite while inserting our new text string.
SQL Server .WRITE() Function Points of Interest
Below is a list of bulleted items or rules for using the .WRITE() function in your SQL queries:
- .WRITE() can only be used on columns with a data type of VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX).
- The @OffsetValue is zero-based (more on that later in this article).
- The @LengthValue is, by default, a BIGINT data type.
- The @LengthValue cannot be a negative number.
- The .WRITE() clause cannot be used on a tuple that is NULL.
As you can see, there are several differences between .WRITE(), STUFF() and REPLACE(). Although the outcome is similar in scope.
SQL Server .WRITE() Function Examples
In the following two sub-sections, we will look at working with the .WRITE() clause to insert and replace data in a generic table we will create. With that said, let’s build a simple table to conduct our testing. Remember to do this on a test server/database, not a production server/database.
In the following three steps, we will create our test table, insert some data, and create a number line to clarify each character’s position. We need the number line to define where we want to insert our new string text.
Building the Test Table
IF OBJECT_ID('myTestTable') IS NOT NULL
DROP TABLE MyTestTable;
GO
CREATE TABLE [dbo].[myTestTable](
[colID] [int] IDENTITY NOT NULL,
[directions] [varchar](max) NULL
)
GO
Inserting Generic Data in Test Table
INSERT INTO [dbo].[myTestTable](directions)
VALUES('North on Pike, South on Lee')
, ('North on Pike, South on Lee')
, ('North on Pike, South on Lee');
GO
Creating a Number Line
I used Microsoft Excel to create the image for the number line. This will play an important role when using the .WRITE() function in our SQL UPDATE statement. With that said, let’s get started learning how to use the SQL .WRITE() function.
Replacing Data with the SQL Server .WRITE() Function
There are two options when using the .WRITE() function inside an UPDATE statement. Option one is to replace a set of values in our tuple with different data, as we’ll do in this section. The second option is to insert our new values into a tuple without replacing any values in that tuple.
In this section, we will replace the word “South” with the word “Right”. Using our number line, we see that the word “South” starts at position 15, which will be our @OffsetValue. There are five characters in the word “South”, which provides us with our @LengthValue.
UPDATE myTestTable
SET directions .WRITE('Right', 15, 5);
GO
Results:
Notice that the word “South” has been replaced with the word “Right” and no other data was affected.
Inserting Data with the SQL Server .WRITE() Function
In this section, we add some data into our tuples without overwriting any existing characters. In this example, we will add the word “then” in front of the word “Right” in our “directions” column.
Again, using our number line from above, we see that the word “Right” replaced the word “South” and “South” started at position 15. Now, follow me on this, it gets a little tricky, but once you grasp it, it will make sense.
I want to add the word “then ” so I will use position 15 as my @OffsetValue and 0 (zero) as my @LengthValue. This will move everything over for the length of my new text. Also, note that I added a space after the word “then” before closing the single quotes. Otherwise, the words “then” and “Right” will run together. The best way to understand this is to see it in action.
UPDATE myTestTable
SET directions .WRITE('then ', 15, 0);
GO
Results:
Working with NULL Values
One of the drawbacks of the .WRITE() function is that it cannot be used to insert data into a tuple that is NULL. If you have a situation where this is needed, you will first need to run a script to insert some temporary data.
Let’s start with building our test table for this exercise. This table will be slightly different from our previous table, but the number line will still be effective.
IF OBJECT_ID('myTestTable') IS NOT NULL
DROP TABLE MyTestTable;
GO
CREATE TABLE [dbo].[myTestTable](
[colID] [int] IDENTITY NOT NULL,
[fName] [VARCHAR](10) NULL,
[directions] [varchar](max) NULL
)
GO
Now, let’s insert two rows of data populating the “fName” column and the “directions” column.
INSERT INTO [dbo].[myTestTable](fName, directions)
VALUES('Name1','North on Pike, South on Lee')
, ('Name2','North on Pike, South on Lee');
GO
Next, we will add one more row of data, but we will only provide the value for the “fName” column. The results will show NULL in the “directions” column, but only for row 3.
INSERT INTO [dbo].[myTestTable](fName)
VALUES('Name3');
GO
Run a simple “SELECT *” query to see our results.
Now that we have our table with a NULL, let’s try to use the .WRITE() function to insert data in that NULL tuple. We’ll add a “WHERE” clause to insert our new data into the “directions” column on row 3 only.
UPDATE myTestTable
SET directions .WRITE('then ', 15, 0)
WHERE colID = 3;
GO
When we run the code snippet, we get the following error:
To avoid this, we first need to place some temporary data in the NULL tuple.
UPDATE [dbo].[myTestTable]
SET directions = N'Replacing NULL value'
WHERE colID = 3;
GO
Results:
Now, we can replace the temporary value with our desired value.
UPDATE [dbo].[myTestTable]
SET directions .WRITE('North on Pike, South on Lee ', 0, 100)
WHERE colID = 3;
GO
Let’s take a good look at that previous code block. There are a couple of differences from that of the previous code blocks. First, we started with the @OffsetValue at 0 (zero), and the @LenghtValue we provided was much larger than what we used before.
Obviously, we wanted to replace all the temporary data with our new data, so we needed to start at the first character position, in this case 0 (zero). Although our temporary data is only 20 characters long, since this is a VARCHAR(MAX) data type, that @LengthValue could also have been one hundred billion. Any value would suffice as long as it’s equal to or greater than the existing character length we want to replace.
If you have a set of tuples with varying lengths, you may find it difficult to find and count the longest entry. There’s a workaround that will save hours of counting the max tuple length. We can use “NULL” as the @LenghtValue instead of putting in an erroneous number.
UPDATE [dbo].[myTestTable]
SET directions .WRITE('North on Pike, South on Lee ', 0, NULL)
WHERE colID = 3;
GO
Results:
Wrap Up
This article discussed how, when, and why you should use the .WRITE()function. While the STUFF() and REPLACE() functions have their place in the SQL world, they cannot perform updates to large or bulk data. That’s where the .WRITE() function comes into play. Unlike the STUFF() and REPLACE() functions that start the @OffsetValue at 1, the .WRITE() function starts with its @OffsetValue at 0 (zero).
Next Steps
