CODEWITHSUNDEEP
sqlsqlite
Arnav Yagnik
Arnav Yagnik

Reputation: 792

SQlite query to update column and replace a value

I want to update a column that contains a string like 12,43,433 I want to only replace 43 with another number say 54 so that the column value becomes 12,54,433.

How can I do that??

Upvotes: 2

Views: 1992

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Storing lists as strings is a very bad idea. SQL has a great data structure for storing lists. It is called a table, not a string. The proper way to store lists is to use a junction table.

Sometimes we are stuck with other people's really bad design decisions. If so, you can do:

update t
    set col = trim(replace(',' || col || ',', ',43,', ',54,'), ',')
    where ',' || col || ',' like '%,43,%';

Notes:

  • This works regardless of where the "43" appears in the string (including at the beginning and end).
  • This maintains the format of the string with no commas at the beginning and end.
  • This only attempts to update rows that have the particular elements in the list.

Use of such a query should really be a stopgap while you figure out how to fix the data structure.

Upvotes: 1

sagi
sagi

Reputation: 40481

You can use REPLACE() function like this:

UPDATE YourTable a
SET a.StringColumn = REPLACE(a.StringColumn,',43,',',54,')
WHERE a.StringColumn like '%,43,%'

Upvotes: 3

Related Questions