SQLite Replace() Function

Here we will learn Replace() function in SQLite and how to use SQLite Replace() function to insert or replace defined string value with examples.

SQLite Replace() Function

In SQLite Replace() function is used to find and replace the particular part of a string or all occurrences of a specified string.

 

The SQLite Replace() function will perform a case-sensitive replacement for the specified string.

Syntax of SQLite Replace() Function

Following is the syntax of SQLite Replace() function to find and replace a particular part of the string.

 

replace( string, find_string, replace_string )

In above SQLite replace() function syntax we defined few parameters those are

 

string – Its source string which is used to find and replace the string.

 

find_string – Its substring which we use to search for the occurrences in the given string to replace with replace_string.

 

replace_string – Its replacement string which we used to replace all occurrences of find_string in the main string.

 

Now we will see how to use SQLite Replace() function to return a number of characters in string with examples.

SQLite Replace() Function Examples

Following are the simple examples of SQLite Length() function to find and replace all occurrences of specified string.

 

sqlite> SELECT replace('aspdotnet-suresh','aspdotnet','Tutlane');

 

replace("aspdotnet-suresh")

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

Tutlane-suresh

 

sqlite> SELECT replace('Aspdotnet-suresh','aspdotnet','Tutlane');

 

replace("Aspdotnet-suresh")

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

Aspdotnet-suresh

 

sqlite> SELECT replace('ABC','abc','XYZ');

 

replace("ABC")

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

ABC

 

sqlite> SELECT replace('Tutlane','Tut','');

 

replace("Tutlane")

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

lane

SQLite Replace() Function with Tables

Now we will see how to use SQLite Replace() function with tables for that create one tabled called “test” and insert data like as shown following.

 

sqlite> CREATE TABLE test(i TEXT);

 

sqlite> SELECT * from test;

 

i

----------

Shweta

Shwets

Shwati

Vinay

Vijay

Vivek

Now we will replace the characters “Shw” with “H” where column i starts with “Shw” like as shown following.

 

sqlite> SELECT replace(i,'Shw','H') FROM test WHERE i LIKE 'Shw%';

 

replace(i,"Shw","H")

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

Heta

Hets

Hati

This is how we can use SQLite Replace() function to replace particular part of the specified string based on our requirements.

Previous
 
Next