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 basic syntax of a REPLACE is as follows:
UPDATE SET REPLACE(column_name, old_string, new_string) WHERE [conditions];
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.
If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.
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;
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;