ORACLE SQL：替换部分文本字段忽略大小写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4051807/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE SQL: Replace part of text field ignoring case
提问by Jez Sprite
I need to replace a path stored in an Oracle DB text field. However paths have been specified with different cases (ie MYPATH, MyPath, Mypath, mypath). When using a combination of REPLACEand UPPER, it does not work as I need it to, ie:
我需要替换存储在 Oracle DB 文本字段中的路径。然而，路径已被指定为不同的情况（即 MYPATH、MyPath、Mypath、mypath）。当使用REPLACEand的组合时UPPER，它不能按我的需要工作，即：
UPDATE Actions
SET Destination = REPLACE(UPPER(Destination), 'MYPATH', 'My_New_Path')
This does the replace but leaves everything in upper case - even for the rows where there is nothing to replace
这会进行替换，但将所有内容都保留为大写 - 即使对于没有任何内容可替换的行
BEFORE: MyPath\FileName- AFTER: My_New_Path\FILENAME
BEFORE: DummyText- AFTER: DUMMYTEXT
之前：MyPath\FileName- 之后：My_New_Path\FILENAME
之前：DummyText- 之后：DUMMYTEXT
What I really need is to replace any occurrence of MyPath, regardless of the case to My_New_Path, without touching the casing on other rows or other part of the field
我真正需要的是替换任何出现的 MyPath，而不考虑 My_New_Path 的大小写，而无需触及其他行或字段其他部分的大小写
Any ideas? I have been scratching my head to no avail...
有任何想法吗？我一直在挠头，无济于事……
PS: Working with Oracle 9...
PS：使用 Oracle 9 ...
回答by Tony Andrews
Perhaps this:
也许这个：
UPDATE Actions
SET Destination = SUBSTR(Destination,1,INSTR(UPPER(Destination), 'MYPATH')-1)
|| 'My_New_Path'
|| SUBSTR(Destination,INSTR(UPPER(Destination), 'MYPATH')+6)
WHERE UPPER(Destination) LIKE '%MYPATH%';
回答by symcbean
I was going to suggest using regexp_replace() but IIRC that's not available in Oracle 9.
我打算建议使用 regexp_replace() 但 IIRC 在 Oracle 9 中不可用。
Something like this then:
然后是这样的：
UPDATE atable
SET afield=SUBSTR(afield, 1, (INSTR(UPPER(afield),'old')-1)
|| 'new'
|| SUBSTR(afield, INSTR(UPPER(afield),'old')+LENGHT('old'), LENGTH(afield))
WHERE afield LIKE '%' || 'old' || '%';
回答by Jez Sprite
A combination + tweaking of the 2 answers made it work:
2个答案的组合+调整使它起作用：
UPDATE actions SET Destination=SUBSTR(Destination, 1, (INSTR(UPPER(Destination),'OLD')-1))
|| 'NEW' || SUBSTR(Destination, INSTR(UPPER(Destination),'OLD')+LENGTH('OLD'), LENGTH(Destination)) WHERE UPPER(Destination) LIKE '%OLD%';
更新操作 SET Destination=SUBSTR(Destination, 1, (INSTR(UPPER(Destination),'OLD')-1))
|| '新' || SUBSTR(Destination, INSTR(UPPER(Destination),'OLD')+LENGTH('OLD'), LENGTH(Destination)) WHERE UPPER(Destination) LIKE '%OLD%';
Thanks guys! - I would vote both answers up but I just signed up and don't have enough reputation...
谢谢你们！- 我会投票给两个答案，但我刚刚注册并且没有足够的声誉......
