SQLite replace() function

Description

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.

Syntax:

replace(str,find_string,replace_with)

SQLite Version: 3.8.5

Arguments:

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:

REPLACE('w3resource','ur','r')
------------------------------
w3resorce

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,
replace(job_id,'ST','VT') 
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
200          AC_ACCOUNT  AC_ACCOUNT

Previous: random()
Next: round()