SQL statement (need help please)

[rwillwervt]

In oracle I have a column that is like...

column
---------------------------------------------------
Aerospace Medicine (A)
Aerospace Medicine (NC)
Aerospace Medicine (NS)
Air Reserve Components: UTA (AF)
Allergy and Immunology (A)
Allergy and Immunology (NC)
Allergy and Immunology (NS)
Anesthesiology (A)
Anesthesiology (AF)
Anesthesiology (NC)
Anesthesiology (NS)
Audiology (A)
Audiology (AF)
Audiology (NC)
Audiology (NS)

I need a distinct select that detects where the (NC) or (NS or (AF) or (A) are and removes them as I need the distnct results that don't factor in the (A) or (NC) or (NS or (AF). Thank you all very much for any help.

[rwillwervt]

the result of the select would be...

Aerospace Medicine
Air Reserve Components: UTA
Allergy and Immunology
Anesthesiology
Audiology

[stecal]

So you're looking for a pattern, and that pattern would be the first occurrence of "(" - find the position and select the all of the string up to that character position.

Here is the opposite condition. You can re-work this to get what you are looking for.

SQL> select distinct substr( col1,( instr(col1,'(')) ) from dbj;

SUBSTR(COL1,(INSTR(COL1,'(')))
--------------------------------------------------
(A)
(AF)
(NC)
(NS)