How to replace a specific string from a large string with Write feature ? TIP #72

 

This is very interesting feature and I recently come to know this awesome feature.

Lets understand this by an example below

Suppose we have a student table with following structure  as shown in below image

 

We have a detail column with VARCHAR(MAX) .

Now as it is VARCHAR(MAX) column it may content a large amount of data currently it has following data as shown in below figure

Suppose we want to replace a specific string from this large column value like we want to replace “interested in” with “Always”  of  studentId = 1 then

we can use write function easily.

With the help of Write we can update a specific text/string  of a large column.

syntax of Write is as shown below

UPDATE TABLENAME

SET COLUMNNAME.WRITE (ReplacedWithString, startPosition, length)

WHERE clause

Now lets understand this by above student example where we want to replace “Interested in” which is doubled by mistake with “always”

So when you run above query you will get following result when you run select command.

great we have replaced the string which we want.

The main benefit of this the entire column value is not logged. suppose you have 2 GB data in your column then instead of logging 2 GB data at the time update only few KB will be logged.

I hope this might helpful to you somewhere.

Thanks !!!

RJ!!!