Complicated use of Substring function

Question

I have a string which contains data like this (with the carriage return)

[GBAP_ABC       ] AAAAAAAAAAAAAAA
[GBAP_DEF       ] BBBBBBBBBBBBBBBBBBBBB

I need to do a substring and retrieve only the AAAAAAAAAAAAAAA from the data. Do you know how I can mention carriage returns. I tried doing with a SUBSTR(COLUMN,INSTR(COLUMN,'GBAP_ABC') + 20, INSTR(COLUMN,'[',INSTR(T.STRING,'GBAP_ABC') + 20 ,1) - 1) but the problem is if the next '[' is not found (I.E. if the data is in the last row), then my query is not working. So if you could tell me how to mention carriage return in my instring that would be helpful.

Answer 1

You could add a '[' on the end (in your expression, not in the DB) so that there will always be one to find. Might need to add a carriage return, too.