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;