Use REGEXP_SUBSTR like a Split function

Question

I need to extract a text value from data in a VARCHAR2 column. Sample:

EDKES^Visit: ^PRIMARY INSURANCE COMMENTS: ^SECONDARY INSURANCE COMMENTS: ^TERTIARY INSURANCE COMMENTS: ^NO PRIMARY INSURANCE^NONE^NONE^NONE^NONE^NONE^NONE^NONE^NONE^NONE^NO SECONDARY INSURANCE^NONE^NONE^NONE^NONE^NONE^NONE^NONE^NONE^NONE^NO TERTIARY INS*

I need to get the text that proceeds the 6th occurrence of the '^' (excluding the '^'). In this example, the text would be NO PRIMARY INSURANCE.

([\w\s\:\*]+(\^?)) mostly works, but doesn't exclude the '^'.

When I try to use this expression REGEXP_SUBSTR(VARCHAR_COL, '([\w\s\:\*]+(\^?))', 1, 6), I get a single character ('s'), rather than the expected match NO PRIMARY INSURANCE^.

What am I missing?

Tomalak · Accepted Answer · 2012-03-13 15:25:21Z

1

This should work pretty well:

REPLACE(REGEXP_SUBSTR(VARCHAR_COL, '[^^]+\^?', 1, 6), '^', '')

answered Mar 13, 2012 at 15:25

Tomalak

339k68 gold badges547 silver badges635 bronze badges

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

5 Comments

craig Over a year ago

Does '^^' mean all characters but '^'?

Tomalak Over a year ago

@craig Exactly, but only when used as a character class: [^^]. Used on its own ^^ it matches the start of the string (twice).

craig Over a year ago

the expression doesn't work as expected if a position doesn't have a value. For example, applying the REGEX you suggested to ONE^TWO^THREE^^FIVE^SIX^SEVEN would return SEVEN, when I need it to return SIX. Can you recommend a solution?

craig Over a year ago

Interestingly, [^^]+\^?? will eliminate the ^ from the results, so the REPLACE function isn't necessary.

Tomalak Over a year ago

@craig Try '[^^]*\^?'. The + requires at least one character, the * makes it optional. I have no way to test it, but it should work.

user557597 · Accepted Answer · 2012-03-13 16:40:55Z

1

You might be able to account for blank columns as well. And if the engine only returns
the capture groups, it will trim the delimiter.

([^^]*).?

This of course means that the last column found is always invalid.

answered Mar 13, 2012 at 16:40

user557597

1 Comment

user557597 Over a year ago

Yeah, it looks like its limited to POSIX style. No lookarounds. If you are sure there will be no blank fields (ie ^^^this^that^^) you should be able to use [^^]+ and this trims the delimeter but it won't accept blank fields.

Collectives™ on Stack Overflow

Use REGEXP_SUBSTR like a Split function

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related