Solved

Oracle: Function help to trim value

Posted on 2013-11-15
5
483 Views
Last Modified: 2013-11-18
Experts,

I have a field called "Request_ID", that contains two merged Request ID's in the form of "000000001098990|000000029558106".  I would like to only show all values before the "|".  So show only "000000001098990".  Is there a way to do this?

this works but, is this the best way because what if the amount of values increase?
SUBSTR ((AU.request_id),0,(LENGTH(AU.request_id)-16)) AS REQUEST_ID_MOD,

Current ouput:          
TKT-1412155      000000001098990|000000029558106
TKT-1412155      000000001098970|000000029558106
TKT-1412155      000000001098991|000000029558106
TKT-1412155      000000001098957|000000029558106

Needing ouput:
TKT-1412155      000000001098990
TKT-1412155      000000001098970
TKT-1412155      000000001098991
TKT-1412155      000000001098957

Thanks
0
Comment
Question by:Maliki Hassani
[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 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39651305
The INSTR function returns the first occurrence of a character in a string.

So, you could do something like:

select substr(col1,1,instr(col1,'|')-1)

This will take a substring of your output string, up to the character before the position of the '|'.

(Assuming that the | character that separates them is always going to be the first occurrence).
0
 

Author Closing Comment

by:Maliki Hassani
ID: 39651317
Thank you!
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 39651324
SELECT SUBSTRING_INDEX(Request_ID, '|', 1)
it will get the value  000000001098990


////////////////////-------SELECT SUBSTRING_INDEX(Request_ID, '|', 2)
///////////////////--------it will get the value  000000029558106

Hope it works for you
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39657881
duncanb7,

Oracle doesn't have a SUBSTRING_INDEX function.
0
 

Author Comment

by:Maliki Hassani
ID: 39657908
Thanks!
0

Featured Post

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!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

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