CODEWITHSUNDEEP
sqloracle-sqldeveloper
VRR2314
VRR2314

Reputation: 1

Extract part of a string from each row of a column

I have a column with rows that have a string. I only need a certain part of the string. All the rows have the part I need starting at the same character number but end at a different place so I could not use that logic to extract it.

For example, these are the cells from four of the rows in this column and I need the parts that say STRUCTURAL_PASSED for the first two and DW_MERGE_PROCESSING for the second two.

If I could extract that part of the string for each of those rows and make a new row with those extracts that would be ideal. 

{"SEQUENCE_NUMBR":55641,"STATUS_OF_FILE_SYSTM":"STRUCTURAL_PASSED","REPORTING_SCHEDULE_NAME":"SPRING_2019"}

{"SEQUENCE_NUMBR":55641,"STATUS_OF_FILE_SYSTM":"STRUCTURAL_PASSED","REPORTING_SCHEDULE_NAME":"SPRING_2019"}

{"SEQUENCE_NUMBR":55641,"STATUS_OF_FILE_SYSTM"::"DW_MERGE_PROCESSING","REPORTING_SCHEDULE_NAME":"SPRING_2019"}

{"SEQUENCE_NUMBR":55641,"STATUS_OF_FILE_SYSTM":"DW_MERGE_PROCESSING","REPORTING_SCHEDULE_NAME":"SPRING_2019"}

Upvotes: 0

Views: 211

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want the sub string starting from position 49 until the double quote excluding.

You can combine SUBSTR and INSTR:

substr(substr(text, 49, 4000), 1, instr(substr(text, 49, 4000), '"') - 1)

Or you can use REGEXP_SUBSTR:

regexp_substr(text, '[^"]+', 49)

Demo: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=ac1a2e8bf3b7a55c6eeede6078e50bdd

Upvotes: 1

Related Questions