Oracle 字符串替换 [英] Oracle string replacement
问题描述
我的 oracle 数据库中有一个列,由于我无法控制的原因包含一个 CSV 字符串,例如
I have a column in my oracle database which due reasons beyond my control contains a CSV string e.g.
项目a、项目b、项目c、项目d
Item a,Item b,Item c,Item d
我想运行一个 UPDATE 语句来删除项目 c.从而结束
I want to run an UPDATE statement to get rid of item c. Thus ending up with
项目a、项目b、项目d
Item a,Item b,Item d
我怎样才能做到这一点
推荐答案
您可以使用 Oracle REPLACE 函数:
You could use the Oracle REPLACE function:
UPDATE table
SET col = replace(col, 'item c', '')
您只需要小心地将其作为 CSV 的一部分进行处理,例如去除以下逗号.这可能意味着先替换item c",然后替换item c"以捕获这两种情况.
You just need to be careful handling it as part of a CSV, e.g stripping a following comma. This could mean replacing 'item c,' first and then replacing 'item c' to capture both cases.
啊,我可能误解了.我的解决方案基于从 CSV 中删除特定字符串 - 如果您希望始终替换第三项,那么 Vincent 的答案就是您需要的
ah, I might have misunderstood. My solution is based on removing a particular string from your CSV - if you are looking to always replace the 3rd item then Vincent's answer is the one you'll need
这篇关于Oracle 字符串替换的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!