Using Regular Expression Oracle

Question

I am looking to use a regular expression to capture the last value in a string. I have copied an example of the data I am looking to parse. I am using oracle syntax.

Example Data:

||CULTURE|D0799|D0799HTT|
||CULTURE|D0799|D0799HTT||

I am looking to strip out the last value before the last set of pipes:

D0799HQTT 
D0799HQTT

I am able to create a regexp_substr that returns the CULTURE:

REGEXP_SUBSTR(c.field_name, '|[^|]+|')

but I have not been able to figure out how to start at the end look for either one or two pipes, and return the values I'm looking for. Let me know if you need more information.

Have You tried splitting the string and picking up last not empty element? — Kamil Wozniak
– Kamil Wozniak, Commented Nov 13, 2013 at 18:32
Like this: array = str.split('|'); int i = array.length; while(array[i].isEmpty()){ i--; } array[i] = yourLastValue; Hope its clear what I wrote. ;) — Kamil Wozniak
– Kamil Wozniak, Commented Nov 13, 2013 at 18:38
@KamilW. How does that answer the question (splitting the string in Oracle using regexes)? — Frank Schmitt
– Frank Schmitt, Commented Nov 14, 2013 at 10:34
@FrankSchmitt Thank You for good question. What I thought is that maybe The Author will use it like in this example: sqltutorials.blogspot.com/2007/09/sql-function-split.html It was just a quick response. Cheers — Kamil Wozniak
– Kamil Wozniak, Commented Nov 14, 2013 at 12:58

A.B.Cade · Accepted Answer · 2013-11-13 18:58:40Z

3

You can try this:

select rtrim(regexp_substr('||CULTURE|D0799|D0799HTT||', 
                     '[[:alnum:]]+\|+$'), '|')
from dual;

Or like this:

select regexp_replace('||CULTURE|D0799|D0799HTT||', 
                     '(^|.*\|)([[:alnum:]]+)\|+$', '\2')
from dual;

Here is a sqlfiddle demo

answered Nov 13, 2013 at 18:58

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

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

3 Comments

user2989011 Over a year ago

Maybe this is easier for "||CULTURE SUBS|INTERNATIONAL TESTING S.A.|ISLAND TEST_PEACE|" I want to return "ISLAND TEST_PEACE". Do you have any suggestions on how to take into consideration spaces and special characters?

A.B.Cade Over a year ago

Instead of [[:alnum:]] use your own class, i.e. [a-zA-Z0-9 _]

user2989011 Over a year ago

ah very good! thank you very much, you helped learn more about regular expressions.

woemler · Accepted Answer · 2013-11-13 19:46:07Z

2

It is probably best to pull out all characters that are not pipes, rather than to assume that they will fit into a specific character set:

select regexp_replace(regexp_substr('||CULTURE SUBS|INTERNATIONAL TESTING S.A.|ISLAND TEST_PEACE|', '[^|]+\|+$'), '\|', '') from dual;

answered Nov 13, 2013 at 19:46

woemler

7,1797 gold badges50 silver badges67 bronze badges

Comments

gpmurthy · Accepted Answer · 2013-11-13 18:32:39Z

1

Consider the following Regex...

(?<=\|)[\w\d]*?(?=\|*$)

Good Luck!

answered Nov 13, 2013 at 18:32

gpmurthy

2,42721 silver badges21 bronze badges

1 Comment

A.B.Cade Over a year ago

Are you sure that Oracle supports lookahead?

the_slk · Accepted Answer · 2013-11-14 09:42:00Z

0

SELECT  REPLACE
        (
            REGEXP_SUBSTR(str, '\w+\W{0,}$')
        ,   '|'
        ) AS result
FROM
(
        SELECT  '||CULTURE|D0799|D0799HTT|'  AS str FROM DUAL UNION ALL
        SELECT  '||CULTURE|D0799|D0799HTT||' AS str FROM DUAL
)
;

answered Nov 14, 2013 at 9:42

the_slk

2,1921 gold badge11 silver badges10 bronze badges

Collectives™ on Stack Overflow

Using Regular Expression Oracle

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related