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

Here's the documentation on regular expressions, but they don't use the substring function in context at all. postgresql.org/docs/9.3/static/functions-matching.html — GotYaNumba
– GotYaNumba, Commented Jan 8, 2016 at 23:46
If I enter 'A' for the '\w', I get some substrings starting with the letter 'A', and those rows that don't match are NULL. I guess I just need to replace '\w' with the correct wildcard for "any alphanumeric." — GotYaNumba
– GotYaNumba, Commented Jan 8, 2016 at 23:53

klin · Accepted Answer · 2016-01-09 01:40:04Z

2

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)

answered Jan 9, 2016 at 1:40

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extracting a string after pattern PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related