Solved

Oracle SQL

Posted on 2011-09-14
10
232 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
[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
  • 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
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!

 
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 77

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
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
Via a live example, show how to take different types of Oracle backups using RMAN.

735 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