Use SUBSTR to get 3rd position with _ delimeter [message #354170] |
Thu, 16 October 2008 15:57  |
tkrofe
Messages: 3 Registered: October 2008 Location: NYC
|
Junior Member |
|
|
Hello,
Sorry if this is in the wrong place of the forum. I have a question to troubleshoot and was hoping anyone could help? Thanks!
I'm trying to pass a filename 'CIM_Importer_AttrNameHere_MMDDYYYY.txt' into the SUBSTR function and return only the 3rd position (after the 2nd '_') of the filename.
So I pass in: CIM_Importer_AttrNameHere_10162008.txt
I'd like to return: AttrNameHere
NOTE: The position of AttrNameHere is not a set length so what I'm really asking is to return everthing between the 2nd '_' and 3rd '_'
Also, the first part of the filename 'CIM_Importer_' will always be the same, and the '_MMDDYYYY.txt' will always be the same length (different dates of course).
Here is what I have so far and I can only get the begining of my output:
SELECT SUBSTR('CIM_Importer_AttrNameHere_10162008.txt',(INSTR('CIM_Importer_AttrNameHere_10162008.txt','_',1,2) + 1)) from dual;
Result:
SUBSTR('CIM_IMPORTER_ATTRNAMEH
------------------------------
AttrNameHere_10162008.txt
All help is appreciated! Thanks!
[Updated on: Thu, 16 October 2008 16:00] Report message to a moderator
|
|
|
Re: Use SUBSTR to get 3rd position with _ delimeter [message #354171 is a reply to message #354170] |
Thu, 16 October 2008 16:10   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@tkrofe,
You can use the third parameter of the SUBSTR Function which stands for the length of the string you want to retrieve.
Quoted from SUBSTR Function
Quote: |
The syntax for the substr function is:
substr( string, start_position, [ length ] )
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.
|
Hope this helps.
Regards,
Jo
[Updated on: Thu, 16 October 2008 16:11] Report message to a moderator
|
|
|
|
Re: Use SUBSTR to get 3rd position with _ delimeter [message #354173 is a reply to message #354172] |
Thu, 16 October 2008 16:23   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
tkrofe wrote on Fri, 17 October 2008 02:43 |
Unfortunately I won't know the length of the section I'm trying to pull from the string. This will be different on every file name.
What I'm trying to get is the substring between the 2nd and 3rd underscores.
|
Oh Thats easy...
1. Use INSTR Function to find the position of the third '_'
2. Use another INSTR Function to find the position of second '_'
3. Step 1 - (Step 2) + 1 will give the required length.
(***Added: Add 1 to the value you get from Step 2.
If the filename is stored in a table use the column instead of issuing the string directly in the SUBSTR Function and the corresponding INSTR Functions.
Combine the above 3 steps and use it as the length parameter in your SUBSTR Function
)
Hope this helps.
Regards,
Jo
[Updated on: Thu, 16 October 2008 22:42] Report message to a moderator
|
|
|
|
|
|