SQLite Replace

Sometimes we wish to replace the substring of a string data field when managing a large database. We have the opportunity to remove the item and then re-adding it with a property. However, using replace in SQLite is a more efficient approach to accomplish this. The REPLACE statement in SQLite is used to discard and re-insert existing entries. The REPLACE SQLite command is a name for the command INSERT OR REPLACE INSERT.

The REPLACE statement in SQLite works on the idea that once UNIQUE or PRIMARY Key constraints fail, it will eliminate the problematic entry and insert a new row. In SQLite, the REPLACE command usually performs a two-step operation. The first one is it starts by deleting the record that violates the constraint and the second one is to add another row. So, if there is a constraint violation in the second step when inserting a new entry, the transaction will be rolled back.

With an example, we’ll learn about SQLite Replace statements and how to use them to remove and reinsert existing entries.

Syntax of the REPLACE statement in SQLite:

The SQLite Replace operation has the following syntax.

>> REPLACE( string, find the SUBSTRING, REPLACE WITH NEW string)

It works by passing three arguments: the “string” containing the substring to be replaced. The “substring” inside that text to be replaced, as well as the string to be “replaced” with the new string.

How to use REPLACE statement in SQLite:

Here, we go with some SQLite replace operation examples and see the usage of it in SQLite. Note that Case-sensitive replacement is taken into account when using the replace function.

We have a simple REPLACE operation in the following figure. First, we need a SELECT statement and then use a REPLACE function to which we have passed three arguments. The first argument is the string “Apple juice” and the second argument is the “Apple” which we want to replace. This “Apple” is a substring from the given string and the third argument is passed with a new string “orange” which is replaced by the substring “Apple”. The following query results are shown in the picture as follows:

>> SELECT REPLACE('Apple juice', 'Apple', 'Orange');

We have our second REPLACE case which also takes three arguments. The first is the string “chicken burger”, next we have substring as “Pizza” which is not the part of the string we have defined. Notice that nothing is changed and the original text is returned if the second parameter is not found in the first argument. The figure shows the query of the REPLACE function and its corresponding results.

>> SELECT REPLACE('Chicken Burger', 'Pizza', 'Pattie');

This one is our third case in which we have our second argument is an empty string. Here, also the REPLACE function changes nothing but returns the source string which we have specified.

>> SELECT REPLACE('Diamond Ring', '', 'Gold');

However, this cannot be true of the third argument. If that string is empty and the first argument contains the second argument. The second argument will be removed from the string as follows:

>> SELECT REPLACE('yellow Light', 'yellow', '');

If the string does not contain our second argument, it returns the string.

>> SELECT REPLACE('Vitamin C', 'Iron', '');

Now, here we are replacing a string with the integer value which is possible in the REPLACE function of SQLite. The result is showing that the string is replaced with the number values.

>> SELECT REPLACE('ID number', 'number', 8);

Example 1: Create a table for using REPLACE function:

We have created a table that is represented as “creams”. We have assigned three columns to this table and also set their data types. Showing the image below, the table is created successfully.

CREATE TABLE creams(
cream_id INT PRIMARY KEY ,
cream_name CHAR(10) ,
cream_price FLOAT

);

Then, we have inserted the values against each column in the table “creams” as follows:

INSERT INTO creams VALUES (1, 'ice-cream', 300 );
INSERT INTO creams VALUES (2, 'chocolate-cream', 600);
INSERT INTO creams VALUES (3, 'cheese-cream', 560);
INSERT INTO creams VALUES (4, 'caramel-cream', 600);
INSERT INTO creams VALUES (5, 'nuts-cream', 450 );

To view the table record, just use the following query and the table record will be displayed as below.

Example 2: Using REPLACE function within the UPDATE statement:

There, we have an UPDATE statement which we have applied to the table “creams”. The update statement sets the values of the table column “cream_name” by calling the SET operator. To the SET operator, we have assigned a REPLACE function and inside the REPLACE function, we have passed three arguments. The first argument is the column name “cream_name” in which we want a replacement. Then, the string “cream” is used as a second argument on which replacement will be implemented. When the REPLACE function finds the string “cream” from the column “cream_name” of the table “creams”, it should change it with the new string “mayo”. The REPLACE function takes the string “mayo” as the third input.

UPDATE
creams
SET
cream_name = REPLACE(cream_name,'cream','mayo');

The changes occur successfully in the following result screen.

Example 3: Using REPLACE function on the unique index in SQLite:

The “cream name” column of the table “creams” will be used to generate an index. I have used the SQLite query below to create a unique index on the field “cream name.”

>> CREATE UNIQUE INDEX idx_creams_cream_name ON creams(cream_name);

Now, we will add a constraint for adding the name of the cream to the table “cream”. If the cream name already exists, it should update that entry. Otherwise, it will need to enter a new cream name. We have simply added this feature by utilizing the SQLite Replace statement as follows.

REPLACE INTO creams (cream_id ,cream_name,cream_price )
VALUES (6,'Garlic-cream', 200);

As the cream_name “Garlic-mayo” does not exist in the table “creams”, the following SQLite REPLACE statement introduces a new row into it. Upon running the select statement, the SQLite shell executes the record of the table creams with the newly inserted record.

Conclusion:

That is the performance of the SQLite Replace function to retrieve the data which we require according to our specifications. We have defined the REPLACE function syntax that we use in SQLite. Then, we have explored some different cases to show how the REPLACE function responds to these cases. At last, we have instances of REPLACE function that we use in the SQLite table.