How to use REPLACE in Sql Server

Intro

PostgreSQL provides the REPLACE string function which allows you to replace substrings in a string column. There is a REPLACE statement which works differently, so it is worth noting this is the REPLACE string function. In this article, we will learn how to use REPLACE in PostgreSQL.

The Syntax

The basic syntax of a REPLACE is as follows:

UPDATE
  SET REPLACE(column_name, old_string, new_string)
  WHERE [conditions];

Getting Setup

For this, we will be using docker. This is recommended for more than just using SQL Server. To find how to install docker go here: https://docs.docker.com/engine/install/

Now create a file called docker-compose.yml and add the following.

version: "3.9"
services:
  db:
    image: "mcr.microsoft.com/mssql/server"
    ports: 
      - 1433:1433
    environment:
        SA_PASSWORD: "Your_password123"
        ACCEPT_EULA: "Y"

Open a terminal and go to the folder the file is located. Then run the following.

docker-compose up

If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.

Creating a DB

We start by creating an employee table to work with.

CREATE TABLE employees (
    first_name VARCHAR (50) NOT NULL,
    last_name VARCHAR (50) NOT NULL
);

Next, we can insert some data to replace later on.

insert into employees (first_name, last_name) 
	values 
	('Keith', 'Holliday'),
	('Jon', 'Doe'),
	('Jane', 'Doe');

And, we can preview the data like so.

SELECT * FROM employees;

Replace Example

Now that we are set up, let's update all 'Doe' strings in the last_name with 'Dane'.

UPDATE employees 
SET 
    last_name = REPLACE(
      last_name,
      'Doe',
      'Dane'
    );

Now, let's see our results.

select  * from employees;