Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle SQL

Posted on 2011-09-14
10
Medium Priority
?
240 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
Independent Software Vendors: 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 500 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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…

618 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