SQLite replace

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

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’.

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

Here is the result.

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

Example of SQLite replace() function using table

Sample table : job_history

employee_id  start_date  end_date    job_id      department_id
-----------  ----------  ----------  ----------  -------------
102          1993-01-13  1998-07-24  IT_PROG     60
101          1989-09-21  1993-10-27  AC_ACCOUNT  110
101          1993-10-28  1997-03-15  AC_MGR      110
201          1996-02-17  1999-12-19  MK_REP      20
114          1998-03-24  1999-12-31  ST_CLERK    50
122          1999-01-01  1999-12-31  ST_CLERK    50
200          1987-09-17  1993-06-17  AD_ASST     90
176          1998-03-24  1998-12-31  SA_REP      80
176          1999-01-01  1999-12-31  SA_MAN      80
200          1994-07-01  1998-12-31  AC_ACCOUNT  90

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.

  1. SELECT employee_id,job_id,  
  2. replace(job_id,'ST','VT')   
  3. FROM job_history  
  4. WHERE department_id<100;   

Here is the result.

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