CODEWITHSUNDEEP
sqlitequery-string
Rahul Vyas
Rahul Vyas

Reputation: 28720

how to remove characters from a string in sqlite3 database?

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?

Upvotes: 12

Views: 26374

Answers (3)

Tarif Chakder
Tarif Chakder

Reputation: 1846

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.

Upvotes: 1

Mark Rushakoff
Mark Rushakoff

Reputation: 258138

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

Upvotes: 14

laalto
laalto

Reputation: 152807

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

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

Upvotes: 13

Related Questions