• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Oracle SQL

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
VenkatBunny
Asked:
VenkatBunny
  • 5
  • 4
1 Solution
 
OP_ZaharinCommented:
- 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
 
VenkatBunnyAuthor Commented:
Length is not fixed
0
 
VenkatBunnyAuthor Commented:
Required information starts with Transaction Number and other the required information the data starts again with Transaction Document Number
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
OP_ZaharinCommented:
- 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
 
VenkatBunnyAuthor Commented:
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
 
OP_ZaharinCommented:
- 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
 
VenkatBunnyAuthor Commented:
Okay I will find out with my team If I can do that..Thanks for responding to my question Zaharin. Appreciated
0
 
OP_ZaharinCommented:
- 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
 
VenkatBunnyAuthor Commented:
Sure Thanks
0
 
slightwv (䄆 Netminder) Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now