REPLACE SQL Update – Simple Method of Finding And Replacing Text With SQL Server

REPLACE is one of the easiest to use and most effective TSQL SQL Server commands.

For those of you looking for a quick example, without having to read a bunch of drivel – here is the example script.

REPLACE – Simple TSQL Example

  • The first parameter is the string of text to search. This could be a column of a table if within a query.
  • Second parameter is the word/string to find.
  • Third parameter is the string to overwrite the second parameter with.
---------------------------------------------------------
SELECT Replace('Text  - Find', 'Find', 'Replaced')
---------------------------------------------------------

The output of running the above, will be as below.

replace result

REPLACE in an update statement

Now lets look at another simple example of the REPLACE command, this time where the data is in a table. We will create a table, insert some data in it, then use REPLACE to search and overwrite a given string of text.

---------------------------------------------------------
USE DBAFire_DB

CREATE TABLE #test_replace (id INT, Info VARCHAR (70))

INSERT INTO #test_replace
SELECT 1,'DBAFire.com is loaded with quick sql tips and tricks'


SELECT 'Before' , *
FROM 
#test_replace

UPDATE #test_replace
SET Info = REPLACE(Info, 'tricks', 'techniques')

SELECT 'After' , *
FROM 
#test_replace

---------------------------------------------------------

You should get the results as below.

replace in column

There are a myriad of use cases for this command. One example could be if an employee got married and changed her surname. If this was scattered through a number of tables you could use the REPLACE command to ensure that nothing got missed.

Another example is for use in test environments. If your database holds any information around server names, or connection strings and you needed to now run the system in a different environment you could again use the command in a reusable script as part of your restore process.


References

Microsoft Official Documentation

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply