• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

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?
0
obrienjimmy
Asked:
obrienjimmy
  • 3
  • 2
1 Solution
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
sdstuberCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
obrienjimmyAuthor Commented:
Brilliant, works perfectly.  Times like this I realize how little I know about Oracle.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
it's rather more about using RegExp than using Oracle ;-)
0
 
obrienjimmyAuthor Commented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now