Solved

Oracle SQL

Posted on 2011-09-14
10
231 Views
Last Modified: 2012-05-12
I have a table and column and below data is stored in that column.

Receipt Application to Transaction Type Transaction Number CR-MACBO1036209 Transaction Document Number


In my output I want to see only  Transaction Number CR-MACBO1036209
value from the column. How do I write the sql query to pull that information is it possible?

0
Comment
Question by:VenkatBunny
  • 5
  • 4
10 Comments
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 36540470
- if the length is fixed, then use substr() function:
SELECT TRIM(SUBSTR(columnname,41,35)) FROM tablename

- to test
SELECT TRIM(SUBSTR('Receipt Application to Transaction Type Transaction Number CR-MACBO1036209 Transaction Document Number',41,35)) FROM dual
0
 

Author Comment

by:VenkatBunny
ID: 36540475
Length is not fixed
0
 

Author Comment

by:VenkatBunny
ID: 36540493
Required information starts with Transaction Number and other the required information the data starts again with Transaction Document Number
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 23

Expert Comment

by:OP_Zaharin
ID: 36540504
- you can use REGEXP_REPLACE to replace the 'Receipt Application to Transaction Type ' and ' Transaction Document Number' with blank. so the remaining output would be 'Transaction Number CR-MACBO1036209':

SELECT
(REGEXP_REPLACE(REGEXP_REPLACE(columname,'\Receipt Application to Transaction Type ',''),'\ Transaction Document Number','')
) TransactionNumber
FROM tablename;

- test
SELECT
(REGEXP_REPLACE(REGEXP_REPLACE('Receipt Application to Transaction Type Transaction Number CR-MACBO1036209 Transaction Document Number','\Receipt Application to Transaction Type ',''),'\ Transaction Document Number','')
) TransactionNumber
FROM DUAL;
0
 

Author Comment

by:VenkatBunny
ID: 36540513
I appreciate your help but since that data in the column only starts with Receipt % as of now this works fine. In future If the users enter other than Receipt % if fails right ?

In other words in a given string I want search and print only it starts with Transaction Number XXXXX
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 36540534
- since the data is not fixed, i would suggest that you redesign that table adding a new column for 'Transaction Number XXXXX' value. that would save you a lot of time figuring how to handle the data to get the required value should the data changed in the future.
0
 

Author Comment

by:VenkatBunny
ID: 36540556
Okay I will find out with my team If I can do that..Thanks for responding to my question Zaharin. Appreciated
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 36540561
- no problem VenkatBunny. to add a bit more, you might not even need 'Transaction Number' value in the new column. Just name that column as Transaction_Number or any columnname convention you have and save only the transaction number value in that column eg: 'CR-MACBO1036209'

OP
0
 

Author Comment

by:VenkatBunny
ID: 36540565
Sure Thanks
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 125 total points
ID: 36542405
It can still be done with regexp_replace.

Take a look at the following.
drop table tab1 purge;
create table tab1(col1 varchar2(4000));

insert into tab1 values('Receipt Application to Transaction Type Transaction Number CR-MACBO1036209 Transaction Document Number');
insert into tab1 values('Transaction Type Transaction Number CR-MACBO1036209 Transaction Document');
commit;

select regexp_replace(col1,'(.*Transaction Number )([a-zA-Z0-9\-]+)( .*)','Transaction Number \2') from tab1;

Open in new window

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
File generation using utl_file 4 53
Need a replacement data type in Oracle 6 77
Oracle and DateTime math 6 37
PL/SQL Two changes 7 27
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

775 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