Extract text inside varchar field

My database is Oracle 8i, I use asp.net to query the database and generate pages; the oracle  table consist of a varchar field "notes" inside which it is always mentioned, somewhere in the text, the AMOUNT:XXX¬ .
I would like to extract the numeric amount in a separate field=XXX¬
I have not found any help about sql query selection inside a varchar field.
Is it possible and how ?

Who is Participating?

Improve company productivity with a Business Account.Sign Up

MarkusIdConnect With a Mentor Commented:
Hmm. interesting. This looks fine over here:
define notes='AB Invited by him for a lunch AMOUNT:999123¬ and blablabla'
select substr('&&notes', instr('&&notes', 'AMOUNT') + 7,
        instr('&&notes', ' ', instr('&&notes', 'AMOUNT'))
           - (instr('&&notes', 'AMOUNT') + 7))
 from DUAL

Open in new window


You should be able to accomplish this using the substr
and instr-functions:

define text='This is a Text with AMOUNT:555.37 in it'
select substr('&&text', instr('&&text', 'AMOUNT') + 7,
                 length('&&text') - instr('&&text', ' ',
                 instr('&&text', 'AMOUNT') + 7) + 1)
from dual

Open in new window

SELECT LEFT(RIGHT(yourcolumn,6),3) from ...
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

In PL/SQL, try:
   lStart VARCHAR2(30) := 'AMOUNT: ';
   lEnd   VARCHAR2(1)  := '¬';
   str    VARCHAR2(30);
   n      NUMBER;
      WITH your_table AS(
      SELECT 'X Y Z ghh amount: 23245.55¬ 65656 A B C' notes
      FROM   dual)
      SELECT substr(notes,
                    instr(upper(notes), lStart) + LENGTH(lStart),
                    instr(notes, lEnd) - (instr(UPPER(notes), lStart) + LENGTH(lStart)) ) str
      INTO   str
      FROM   your_table;
      n := str;

Open in new window

talentiumAuthor Commented:
Thanks for all your quick reply !
I am actually going to use MarkusId solution, a simple query in sql against the database->this will be made with a sqldatasource in visual studio.
The last problem I have is that the field notes contain text after AMOUNT:
By example, this is a typical notes field:
"AB Invited by him for a lunch AMOUNT:999123¬ and blablabla "
This is the query I have adapted:
select substr(notes, instr(notes, 'AMOUNT') + 7, length(notes) - instr(notes, ' ', instr(notes, 'AMOUNT') + 7) + 1) from JOURNAL where JOURNALID='11556'
This is the result:
999123¬ and blablabla

So, I need in my SQL query to setup that it takes the text after amount, but stop after 7 characters or after the euro symbol ?

Thanks for your help

Sorry, I found the bug. Try this:

select substr(notes, instr(notes, 'AMOUNT') + 7, instr(notes, ' ', instr(notes, 'AMOUNT')) - (instr(notes, 'AMOUNT') + 7))
 from JOURNAL where JOURNALID='11556'

Open in new window

talentiumAuthor Commented:
I have try, no error, but nothing in the select (empty)...
awking00Connect With a Mentor Commented:
substr(notes, instr(notes, 'AMOUNT') + 7) => This will get you everything after amount.
So, substr(substr(notes, instr(notes, 'AMOUNT') + 7),1,7) will just get the first 7 characters of that. If the amount is not always 6 digits plus the currency symbol, you could use instr() - 1 of the string after the 'AMOUNT:' searching for the space as your length parameter.
talentiumAuthor Commented:
Thanks both for your help and details
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.