Solved

INSTR search for NOT a number.

Posted on 2013-11-04
6
380 Views
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?
0
Comment
Question by:obrienjimmy
  • 3
  • 2
6 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Closing Comment

by:obrienjimmy
Comment Utility
Brilliant, works perfectly.  Times like this I realize how little I know about Oracle.
0
 
LVL 13

Expert Comment

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

Author Comment

by:obrienjimmy
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now