Solved

Extract text inside varchar field

Posted on 2009-07-16
9
566 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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

Technology Partners: 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!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

685 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