Solved

Oracle SQL

Posted on 2011-09-14
10
230 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
Comment Utility
- 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
Comment Utility
Length is not fixed
0
 

Author Comment

by:VenkatBunny
Comment Utility
Required information starts with Transaction Number and other the required information the data starts again with Transaction Document Number
0
 
LVL 23

Expert Comment

by:OP_Zaharin
Comment Utility
- 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
Comment Utility
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
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
Comment Utility
- 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
Comment Utility
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
Comment Utility
- 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
Comment Utility
Sure Thanks
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 125 total points
Comment Utility
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.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

728 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

14 Experts available now in Live!

Get 1:1 Help Now