oracle 如何在oracle中删除破折号后的文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6202383/
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
How to remove text following a dash in oracle?
提问by user560785
I was looking at
我在看
replace('ABC-DEF', '-')
and
和
regexp_replace('ABC-DEF', '-$')
to help me remove all characters after a dash but I haven't been able to find a good function or expression that matches what I want to do. What is a good oracle expression that does this?
帮助我删除破折号后的所有字符，但我一直无法找到与我想要做的匹配的好的函数或表达式。有什么好的 oracle 表达式可以做到这一点？
ABC-DEF => ABC
回答by Craig
I would just use substr to get everything up to the dash:
我只想使用 substr 将所有内容都添加到破折号：
select substr(str, 1, instr(str, '-')-1) new_str
from
(
select 'ABC-DEF' str
from dual
)
回答by Dave Costa
To do it with a regular expression:
用正则表达式来做：
regexp_replace('ABC-DEF', '-.*')
回答by Joe Stefanelli
SUBSTR('ABC-DEF', 1, INSTR('ABC-DEF', '-')-1)
回答by carny666
select substr('hello-dolly', 1, instr('hello-dolly', '-')-1) from dual
回答by Dan
SELECT regexp_replace('ABC-DEF', '[-]',NULL) FROM dual; SELECT regexp_replace('ABC-DEF-GHI-JKL', '[-]',NULL) FROM dual;
SELECT regexp_replace('ABC-DEF', '[-] ',NULL) FROM dual; SELECT regexp_replace('ABC-DEF-GHI-JKL', '[-]',NULL) FROM dual;
OR Use my cool function called FIELD:
或者使用我很酷的函数 FIELD：
CREATE OR REPLACE FUNCTION field(i_string VARCHAR2
,i_delimiter VARCHAR2
,i_occurance NUMBER
,i_return_number NUMBER DEFAULT 0
,i_replace_delimiter VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
v_return_string VARCHAR2(32767);
n_start NUMBER := i_occurance;
v_delimiter VARCHAR2(1);
n_return_number NUMBER := i_return_number;
n_max_delimiters NUMBER := regexp_count(i_string, i_delimiter);
BEGIN
IF i_return_number > n_max_delimiters THEN
n_return_number := n_max_delimiters + 1;
END IF;
FOR a IN 1 .. n_return_number LOOP
v_return_string := v_return_string || v_delimiter || regexp_substr(i_string, '[^' || i_delimiter || ']+', 1, n_start);
n_start := n_start + 1;
v_delimiter := nvl(i_replace_delimiter, i_delimiter);
END LOOP;
RETURN(v_return_string);
END field;
Usage: SELECT field('Hello-Dolly', '-', 1, 2) Entire_string ,field('Hello-Dolly', '-', 1, 1) First_part ,field('Hello-Dolly', '-', 2, 1) Second_part FROM dual;
用法：SELECT field('Hello-Dolly', '-', 1, 2) Entire_string ,field('Hello-Dolly', '-', 1, 1) First_part ,field('Hello-Dolly', '-' , 2, 1) Second_part FROM dual;
