CODEWITHSUNDEEP
sqloracle-database
Pranay
Pranay

Reputation: 63

How to replace a comma separated value in table column with user input value oracle


I have a table in oracle with a column with comma separated values. What i need is when a user enters a value and if that value is present in any of the rows, it should be removed.
eg.

COL

123,234
56,123

If user enters 123, the 1st column should have only 234 and second row should have only 56.
How do we do this in oracle??
Please help
Thanks

Upvotes: 0

Views: 2529

Answers (2)

GolezTrol
GolezTrol

Reputation: 116190

delete from yourtable t
where
  instr(','||t.col||',', '123') > 0

You can replace '123' with a parameter if you like.

But a better way would be not to store comma separated values, and create a detail table instead. If you need to look for a specific value within a comma separated list, you cannot make use of indices, amongst other limitations.

[edit] Misunderstood the question. You meant this:

update YourTable t
set
  t.col = substr(substr(replace(','||t.col||',', ',123,', ','), 2), -2)
where
  instr(','||t.col||',', '123') > 0
  • Add ',' before and after to match items at the beginning or end of the value.
  • Replace using the value ',123,' (within comma's) to prevent accidentally matching 1234 too.
  • Use substr twice to remove the first and last character (the added commas)
  • Use instr in the where to prevent updating records that don't need to be updated (better performance).

Upvotes: 3

mcha
mcha

Reputation: 2998

try this : 

UPDATE t
SET col = REPLACE(REPLACE(col, '&variable', ''), ',', '') FROM t ;

Upvotes: 1

Related Questions