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

structure 

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

Default_data

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

writeFunction

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

result_after_Write

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!!!

Advertisements

2 thoughts on “How to replace a specific string from a large string with Write feature ? TIP #72

  1. Hi Rajat,

    Can’t we use below update query ?

    update tblstudentsource set
    detail = replace(detail,’Interested In’,’Always’)
    where id =1

    I know this will update “Interested In” word with “Always” everywhere but let’s say in the whole sentence there is only one Interested In word then what is the difference between your query and mine.?

    With the combination of substring & replace function, We can achieve the same.

    Is there any benefit of Write feature?

    Thanks
    Nutan Patel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s