how to remove characters from a string in sqlite3 database?

Question

i have a string like this a) Text in my sqlite databse..i want to remove a) from databse..anyone know a query for this?

Answer 1

@laalto's answer is close, but it will not work on edge cases, specifically if 'a) ' occurs elsewhere in the string. You want to use SUBSTR to only remove the first 3 characters.

sqlite> SELECT REPLACE ("a) I have some information (or data) in the file.", "a) ", "");
I have some information (or datin the file.

sqlite> SELECT SUBSTR ("a) I have some information (or data) in the file.", 4);
I have some information (or data) in the file.

So updating his query, it should turn into:

UPDATE tbl SET col=SUBSTR(col, 4) WHERE col LIKE 'a) %';

... noting that strings are indexed from 1 in SQLite.

Answer 2

You can use REPLACE also to delete parts of a string:

UPDATE tbl SET col=REPLACE(col, 'a) ', '') WHERE col LIKE 'a) %';

Answer 3

I think this code help you.

UPDATE TABLE_NAME SET COLUMN_NAME=REPLACE(COLUMN_NAME,'xxx','') WHERE COLUMN_NAME LIKE 'xxx%;

from the above code 'xxx' your unwanted string that is replaced '' when the string starts with xxx all of the row in the column that you will be chosen.