Solved

Extract text inside varchar field

Posted on 2009-07-16
9
568 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

726 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