Write Function in SQL Server 2005

Something cool I just ran across today.  It appears that in SQL 2005 they added the 'Write' function.

This allows you to update character column, including that with the size of MAX.  This is useful as STUFF didn't work with varchar(max).

-- Create a Temp table to hold our text
CREATE TABLE #tmpTable
    (
        myText NVARCHAR(MAX)
    )


-- Insert sample text we want to mess with
INSERT INTO #tmpTable
Select 'This a very cool Test!'


-- Use the Write function to put the text 'Hello Test'
-- at the beginning of the field
UPDATE #tmpTable
    SET myText.write(
                        'Hello Test'
                        ,0              -- Start index
                        ,0              -- Number of characters to replace
                    )


-- Show that the text has indeed been pushed to the front of the field
select * FROM #tmpTable


-- Now lets use the Write function to delete the first ten characters
UPDATE #tmpTable
    SET myText.Write(
                        ''              -- An Empty String
                       , 0              -- Start at 0
                       , 10             -- The number of characters to replace
                    )