INSTR search for NOT a number.

Posted on 2013-11-04
Last Modified: 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?
Question by:obrienjimmy
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 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39621498
just a thought:

how about using RegExp as shown here:

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

Accepted Solution

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


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

if not

then change


LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39621519
If you're running 9i, you could use one of the custom regexp functions found throughout the web, like

or just google it ;-)
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Closing Comment

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

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39621535
it's rather more about using RegExp than using Oracle ;-)

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.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle RMAN - trying to duplicate a database 5 52
Help on model clause 5 47
pl/sql parameter is null sometimes 2 25
scheduler notification 9 39
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

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

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

Join & Ask a Question