Solved

Extract text inside varchar field

Posted on 2009-07-16
9
563 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP:Radiobuttonlist/asp:RadioButtonListItem custom styling 1 21
Error on Add method 1 38
PL/SQL Display based on value 4 21
Help Extract Specific in SQL 8 27
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

863 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

26 Experts available now in Live!

Get 1:1 Help Now