INSTR search for NOT a number.
Posted on 2013-11-04
Basically, I'm extracting some data from from a CLOB. I know the starting point, but the end point is unkown. It is when the number ends. I was first assuming that there was a space so had the following code
(substr(clbtext, instr(clbtext,'DS-',1), instr(clbtext,' ',instr(clbtext,'DS-',1)))) as test
But it could also be a full stop or an end of line. And possibly ones I dont' know of.
So, I'm wondering can I extract the text from the starting point until the number ends.
So, what I am looking for will always start with 'DS-' Can be followed by a two or three letter string and another dash e.g. 'TST-', then the 4 digit number
So what I'm looking to do is something like:
(substr(clbtext, instr(clbtext,'DS-',1), instr(clbtext,'NOT A NUMBER',instr(clbtext,'DS-',1)+7))) as test
How can I go about achieving this?