Solved

Extract text inside varchar field

Posted on 2009-07-16
9
565 Views
Last Modified: 2013-12-19
Dear,
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 ?

thanks
0
Comment
Question by:talentium
9 Comments
 
LVL 9

Expert Comment

by:MarkusId
ID: 24867798
Hi,

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

0
 
LVL 14

Expert Comment

by:racek
ID: 24867893
SELECT LEFT(RIGHT(yourcolumn,6),3) from ...
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24867941
In PL/SQL, try:
DECLARE
   lStart VARCHAR2(30) := 'AMOUNT: ';
   lEnd   VARCHAR2(1)  := '¬';
   str    VARCHAR2(30);
   n      NUMBER;
BEGIN
      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;
      dbms_output.put_line(n);
END;

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:talentium
ID: 24869102
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
 
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 24869314
Hi,

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

0
 

Author Comment

by:talentium
ID: 24869416
I have try, no error, but nothing in the select (empty)...
0
 
LVL 9

Accepted Solution

by:
MarkusId earned 350 total points
ID: 24869523
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
/
 
SUBSTR(
-------
999123¬
 
SQL> 

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 150 total points
ID: 24953951
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.
0
 

Author Closing Comment

by:talentium
ID: 31604150
Thanks both for your help and details
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

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…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

765 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