asked on

INSTR search for NOT a number.

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?

Alex [***Alex140181***]

just a thought:

how about using RegExp as shown here: http://psoug.org/reference/regexp.html

you might use REGEXP_SUBSTR in conjunction with "[:digit:]"...

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alex [***Alex140181***]

If you're running 9i, you could use one of the custom regexp functions found throughout the web, like http://phil-sqltips.blogspot.de/2009/06/regexpreplace-regexplike-for-oracle-9i.html

or just google it ;-)

obrienjimmy

ASKER

Brilliant, works perfectly. Times like this I realize how little I know about Oracle.

Alex [***Alex140181***]

it's rather more about using RegExp than using Oracle ;-)

obrienjimmy

ASKER

I know. I only work with oracle / code in plsql every now and again, not part of my main job. I've only used regexp once before I think. I need to learn more about it, it obviously is very useful.