Solved

# INSTR search for NOT a number.

Posted on 2013-11-04
Medium Priority
400 Views
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?
0
Question by:obrienjimmy
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 3
• 2

LVL 14

Expert Comment

ID: 39621498
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:]"...
0

LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 39621504
try this...

regexp_substr(clbtext,'DS-[A-Z]{2,3}-[0-9]+')

I made the assumption that your 2 or 3 letter string would always be capital letters

if not

then change

[A-Z]

to
[A-Za-z]
0

LVL 14

Expert Comment

ID: 39621519
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 ;-)
0

Author Closing Comment

ID: 39621527
Brilliant, works perfectly.  Times like this I realize how little I know about Oracle.
0

LVL 14

Expert Comment

ID: 39621535
it's rather more about using RegExp than using Oracle ;-)
0

Author Comment

ID: 39621544
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.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
###### Suggested Courses
Course of the Month15 days, 3 hours left to enroll

#### 649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.