string 从 Oracle 上的给定位置删除一个字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2916488/
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
Remove a character from a given position on Oracle
提问by alex
Is there anyway to remove a character from a given position?
无论如何要从给定位置删除字符？
Let's say my word is: PANCAKES And I want to remove the 2nd letter (in this case, 'A'), so i want PNCAKES as my return.
让我们说我的话是：PANCAKES 我想删除第二个字母（在这种情况下，'A'），所以我想要 PNCAKES 作为我的回报。
Translate doesnt work for this. Replace doesnt work for this. Regex is damn complicated...
翻译对此不起作用。替换对此不起作用。正则表达式真是太复杂了......
Ideas?
想法？
回答by OMG Ponies
Example:
例子：
SUBSTR('PANCAKES', 0, INSTR('PANCAKES', 'A', 1, 1)-1) || SUBSTR('PANCAKES', INSTR('PANCAKES', 'A', 1, 1)+1)
I don't have an Oracle instance to test with, might have to tweak the -1/+1 to get the position correct.
我没有要测试的 Oracle 实例，可能需要调整 -1/+1 才能使位置正确。
References:
参考：
回答by AntonLosev
You should strongly consider using regexp_replace. It is shorter and not so complicated as it seems at a first glance:
您应该强烈考虑使用regexp_replace. 它更短，也没有乍一看那么复杂：
SELECT REGEXP_REPLACE( S, '^(.{1}).', '' )
FROM (
SELECT 'PANCAKES'
FROM DUAL
)
The pattern ^(.{1}).searches from the start of the string ( denoted by ^) for exactly one ( .{1}) of printable or uprintable characters followed by again just one of those characters ( .). The "exact" part is closed in parenthesis so it can be referenced as match group by it's number in the third function's argument ( \1). So the whole substring matched by regexp is 'PA', but we reference only 'P'. The rest of the string remains untouched. So the result is 'PNCAKES'.
该模式^(.{1}).从字符串的开头（由 表示^）搜索恰好一 ( .{1}) 个可打印或可打印的字符，然后再次仅搜索这些字符之一 ( .)。“精确”部分用括号括起来，因此可以通过它在第三个函数的参数 ( \1) 中的编号作为匹配组引用。所以正则表达式匹配的整个子串是'PA'，但我们只引用'P'。字符串的其余部分保持不变。所以结果是“PNCAKES”。
If you want to remove N-th character from the string just replace number 'one' in the pattern (used to remove second character) with the value of N-1.
如果您想从字符串中删除第 N 个字符，只需将模式中的数字“一”（用于删除第二个字符）替换为 N-1 的值。
It's good for programmer or any kind of IT specialist to get familiar with regular expressions as it gives him or her a lot of power to work with text entries.
熟悉正则表达式对程序员或任何类型的 IT 专家都有好处，因为它赋予了他或她处理文本条目的强大能力。
回答by Jan
Or use a custom made SplitAtPos function using SUBSTR. Advantage is that it still works on Oracle v9.
或者使用 SUBSTR 使用定制的 SplitAtPos 函数。优点是它仍然适用于 Oracle v9。
set serveroutput on
declare
s1 varchar2(1000);
s2 varchar2(1000);
function SplitAtPos(s in out varchar2, idx pls_integer)
return varchar2
is
s2 varchar2(1000);
begin
s2:=substr(s,1,idx-1);
s:=substr(s,idx,length(s)-idx+1);
return s2;
end;
begin
s1:='Test123';
s2:=SplitAtPos(s1,1);
dbms_output.put_line('s1='||s1||' s2='||s2);
s1:='Test123';
s2:=SplitAtPos(s1,5);
dbms_output.put_line('s1='||s1||' s2='||s2);
s1:='Test123';
s2:=SplitAtPos(s1,7);
dbms_output.put_line('s1='||s1||' s2='||s2);
s1:='Test123';
s2:=SplitAtPos(s1,8);
dbms_output.put_line('s1='||s1||' s2='||s2);
s1:='Test123';
s2:=SplitAtPos(s1,0);
dbms_output.put_line('s1='||s1||' s2='||s2);
end;
回答by JPIN
You can use something like this in pl/SQL
你可以使用这样的东西 pl/SQL
DECLARE
v_open NUMBER;
v_string1 VARCHAR2(10);
v_string2 VARCHAR2(10);
v_word VARCHAR2(10);
BEGIN
v_open := INSTR('PANCAKES' ,'A',1);
v_string1 := SUBSTR('PANCAKES' ,1, 1);
v_string2 := SUBSTR('PANCAKES' ,v_open+1);
v_word := v_string1||v_string2;
END;
回答by Randy
yes REPLACE and SUBSTR in the proper order will do the trick.
是的，按正确顺序的 REPLACE 和 SUBSTR 可以解决问题。
the end result should be a concatenation of the SUBSTR before the removed char to the SUBSTR after the char.
最终结果应该是将删除的字符之前的 SUBSTR 连接到字符之后的 SUBSTR。
if the entire column is only one word, then you can just do an update, if the word is in another string, then you could use REPLACE as a wrapper.
如果整个列只有一个词，那么您可以进行更新，如果该词在另一个字符串中，那么您可以使用 REPLACE 作为包装器。
