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??
Add a comment
|
2 Answers
You can use REPLACE() function like this:
UPDATE YourTable a
SET a.StringColumn = REPLACE(a.StringColumn,',43,',',54,')
WHERE a.StringColumn like '%,43,%'
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.
1 Comment
Arnav Yagnik
Thanks for the heads up about db design , and for the answer, i know what should be done now :)