?
Solved

Oracle: Function help to trim value

Posted on 2013-11-15
5
Medium Priority
?
497 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
5 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 2000 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 78

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup
Suggested Courses

621 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