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];
We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called
docker-compose.yml and add the following.
version: '3' services: db: image: 'postgres:latest' ports: - 5432:5432 environment: POSTGRES_USER: username POSTGRES_PASSWORD: password POSTGRES_DB: default_database volumes: - psqldata:/var/lib/postgresql phpmyadmin: image: phpmyadmin/phpmyadmin links: - db environment: PMA_HOST: db PMA_PORT: 3306 PMA_ARBITRARY: 1 restart: always ports: - 8081:80 volumes: psqldata:
Now, navigate to
http://localhost:8081/ to access phpMyAdmin. Then log in with the username
root and pass
Click the SQL tab and you are ready to go.
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;