SQLite replace() function


The replace() function returns a string formed by substituting string describe in the third parameter for every occurrence of string describe in the 2nd parameter from the string describe in 1st parameter.



SQLite Version: 3.8.5


Name Description
str A string.
find_string A string which is present one or more times within the string str.
replace_with A string which will replace every time it finds find_string within str.

Example of SQLite replace() function

The following SQLite statement replaces every time it finds ‘ur’ within the ‘w3resource’ by ‘r’.

SELECT REPLACE('w3resource','ur','r');

Here is the result.

Sample Output:


Example of SQLite replace() function using table

Sample table: job_history

The following SQLite statement replaces all the occurrences of ‘ST’ with 'VT' within the column job_id from the table job_history for those rows, in which the column value of department_id is less than 100.

SELECT employee_id,job_id,
FROM job_history
WHERE department_id<100;

Here is the result.

Sample Output:

employee_id  job_id      replace(job_id,'ST','VT')
-----------  ----------  -------------------------
102          IT_PROG     IT_PROG
201          MK_REP      MK_REP
114          ST_CLERK    VT_CLERK
122          ST_CLERK    VT_CLERK
200          AD_ASST     AD_ASVT
176          SA_REP      SA_REP
176          SA_MAN      SA_MAN

Previous: random()
Next: round()

Follow us on Facebook and Twitter for latest update.

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook