Solved

Extract text inside varchar field

Posted on 2009-07-16
9
562 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 31

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
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 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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

707 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

12 Experts available now in Live!

Get 1:1 Help Now