How to remove text following a dash in oracle?
I was looking at
开发者_Python百科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?
ABC-DEF => ABC
I would just use substr to get everything up to the dash:
select substr(str, 1, instr(str, '-')-1) new_str
from
(
select 'ABC-DEF' str
from dual
)
To do it with a regular expression:
regexp_replace('ABC-DEF', '-.*')
SUBSTR('ABC-DEF', 1, INSTR('ABC-DEF', '-')-1)
select substr('hello-dolly', 1, instr('hello-dolly', '-')-1) 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:
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;
精彩评论