Solved

Oracle: Function help to trim value

Posted on 2013-11-15
5
484 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

630 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