Extract variable string between given strings in oracle sql

Question

Can you please help me extract the status of every sequence from below, in Oracle SQL? I need to get 'finished' or 'started' or whatever new status the previous sequence may have in that concatenation, that is a result from a previous query.

I need to search by 'account' and get 'finished' in the first case and null in the second case OR search by 'book' and get 'started' in the first case and 'finished' for the second case.

STATUS
==========================================================================
sequence_account;finished;sequence_book;started;sequence_content;finished;
sequence_book;finished;

Fix your data model. Don't store multiple values in a single string. — Gordon Linoff
– Gordon Linoff, Commented Jan 13, 2020 at 11:29
Please edit your question with a detailed description of the value that you are expecting to be extracted and the steps that you expect the program to take (in English not code). "Or whatever new status the previous sequence may have" is not helpful. How do you define a sequence? Where are you searching by "account" or by "book"? What are the "first" and "second" cases? Where do we find a "status"? — MT0
– MT0, Commented Jan 13, 2020 at 11:36
Also, why use concatenation in the previous step and make this step more complicated? Why not just skip the concatenation in the previous step and directly use the unconcatenated values as its going to be easier. — MT0
– MT0, Commented Jan 13, 2020 at 11:39

MT0 · Accepted Answer · 2020-01-13 11:45:28Z

3

Assuming you are using the pattern ;sequence_<term>;<value>; then just search for the sub-strings that match:

Oracle Setup:

CREATE TABLE your_query ( status ) AS
SELECT 'sequence_account;finished;sequence_book;started;sequence_content;finished;' FROM DUAL UNION ALL
SELECT 'sequence_book;finished;' FROM DUAL

Query:

SELECT REGEXP_SUBSTR( status, '(^|;)sequence_book;(.*?);', 1, 1, NULL, 2 ) AS book,
       REGEXP_SUBSTR( status, '(^|;)sequence_account;(.*?);', 1, 1, NULL, 2 ) AS account
FROM   your_query

Output:

BOOK     | ACCOUNT 
:------- | :-------
started  | finished
finished | null

db<>fiddle here

However, you state that the input is a query you are generating using concatenation. Why bother with the concatenation and, instead, just filter the unconcatenated values.

answered Jan 13, 2020 at 11:45

MT0

173k12 gold badges70 silver badges136 bronze badges

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

2 Comments

Criss Over a year ago

Thank you very much, this is exactly what I need. The concatenation is a result from DataStage and I need to work with it like this.

Abra Over a year ago

@Criss please accept this answer by clicking on the big, gray tick mark below the number of up-votes.

Collectives™ on Stack Overflow

Extract variable string between given strings in oracle sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related