Extracting a string after pattern PostgreSQL

Question

So I have a column like the following:

diagnosis

715.16 oSTEOARTHRITIS, LOWER-LEG 
715.17 - OSTEOARTHRITIS, ANKLE AND FOOT
715.90 oSTEOARTHRITIS, UNSPECIFIED
716.10 - TRAUMATIC ARTHROPATHY - UNSPECIFIED

Some entries have initial dashes, some do not. Some have dashes later in the string.

I'd like to select the substring (in itallics) with the pattern:

(any chars)(dash)(space)(any alphanumeric)(the rest of the string)

OR

(any chars)(space)(any alphanumeric)(the rest of the string)

My query goes: select substring(diagnosis from '% #"\w%#"' for '#') from TableICDdict;

but it just returns all the rows with empty strings in them (no errors). Any ideas on how to actually output the extracted substrings?

Thanks

Answer 1

Use regexp_replace() to skip all characters preceding first space (and the space) and ltrim() to skip additional '- ' strings:

with t(diagnosis) as (values 
    ('715.16 oSTEOARTHRITIS, LOWER-LEG'),
    ('715.17 - OSTEOARTHRITIS, ANKLE AND FOOT'),
    ('715.90 oSTEOARTHRITIS, UNSPECIFIED'),
    ('716.10 - TRAUMATIC ARTHROPATHY - UNSPECIFIED'))
select ltrim(regexp_replace(diagnosis, '.*? (.*)', '\1'), '- ') result
from t;

               result                
-------------------------------------
 oSTEOARTHRITIS, LOWER-LEG
 OSTEOARTHRITIS, ANKLE AND FOOT
 oSTEOARTHRITIS, UNSPECIFIED
 TRAUMATIC ARTHROPATHY - UNSPECIFIED
(4 rows)