REPLACE in SQL Server


REPLACE function in SQL is used to replace all the occurrences of a specified string, in a given string. It’s syntax is

REPLACE(existing_string, string_to_replace, replacement_string)

Here,

  • existing_string is the string in which we would like to replace all the occurrences specific string. This could be any column of an existing table also.
  • string_to_replace is the string, of which, all the occurrences are to be replaced in the existing string.
  • replacement_string is the new string that we would like to be replaced in place of string_to_replace.

Let’s use a simple example to discuss this concept now. See the commands below :

Replace in SQL Server

We have declared a simple string and applied the replace function, to replace all the occurrences of ‘Test’ with ‘updated’. It will simply REPLACE all the occurrences of ‘Test’ in the existing string. Similarly we could have used any table column in the Select statement above.

STUFF vs REPLACE

It might sound quite similar to the stuff function but they are different. Stuff can be only used to replace a particular string in our existing string. But, replace will replace all the occurrences of a string, in the existing string. Also, stuff may or may not replace any characters in the string, but replace will always replace the specified characters in the string. In other words, stuff can play the role of insertion and replacement in a string, but replace will always play the role of replacement in a string.

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in SQL Server and tagged . Bookmark the permalink.

I have a suggestion..

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