[Solved] extract sub string from string in oracle

by


Use a regular expression to replace everything from each ~ tilde character until the next comma , or pipe | character (exclusive of that final character):

Oracle Setup:

CREATE TABLE your_data ( input_string ) AS
SELECT '(,QUESTION-3914~Please enter the unique identification number associated with the IRQ.|3~Greater Than|5~5,AND,QUESTION-3920~Select the contract action that applies to this IRQ.|5~Equal To|LOV1274~New Agreement,),AND,QUESTION-3921~If "New Agreement@comma@" which type of New Agreement is being requested?|5~Equal To|y~Yes,OR,NOT,(,QUESTION-3923~Will the Third Party Relationship support the implementation@comma@ pilot@comma@ launch@comma@ or operation of a New Activity (as defined in the New Activity Risk Management Policy)?|5~Equal To|y~Yes,)' FROM DUAL

Query:

SELECT REGEXP_REPLACE( input_string, '~.*?([|,])', '\1' ) AS output
FROM   your_data d

Output:

| OUTPUT                                                                                               |
| :--------------------------------------------------------------------------------------------------- |
| (,QUESTION-3914|3|5,AND,QUESTION-3920|5|LOV1274,),AND,QUESTION-3921|5|y,OR,NOT,(,QUESTION-3923|5|y,) |

db<>fiddle here

solved extract sub string from string in oracle