Home » SQL & PL/SQL » SQL & PL/SQL » Use SUBSTR to get 3rd position with _ delimeter
Use SUBSTR to get 3rd position with _ delimeter [message #354170] Thu, 16 October 2008 15:57 Go to next message
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 Go to previous messageGo to next message
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 #354172 is a reply to message #354171] Thu, 16 October 2008 16:13 Go to previous messageGo to next message
tkrofe
Messages: 3
Registered: October 2008
Location: NYC
Junior Member
Thanks Jo. Smile

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.

If you have any other insight, awesome!

Thanks for the response!
Re: Use SUBSTR to get 3rd position with _ delimeter [message #354173 is a reply to message #354172] Thu, 16 October 2008 16:23 Go to previous messageGo to next message
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

Re: Use SUBSTR to get 3rd position with _ delimeter [message #354195 is a reply to message #354170] Thu, 16 October 2008 23:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


By the way almost the same issue was posted recently . Please check it.

Thumbs Up
Rajuvan.
Re: Use SUBSTR to get 3rd position with _ delimeter [message #354232 is a reply to message #354170] Fri, 17 October 2008 02:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're on 10g or higher, then it's easy:
select regexp_substr('CIM_Importer_AttrNameHere_MMDDYYYY.txt'
                    ,'[^_]+'
                    ,1,3)
from dual;
Re: Use SUBSTR to get 3rd position with _ delimeter [message #354297 is a reply to message #354173] Fri, 17 October 2008 08:45 Go to previous message
tkrofe
Messages: 3
Registered: October 2008
Location: NYC
Junior Member
Thanks everyone! With your advice I've worked it out and everything is now working as planned.

Smile Tim
Previous Topic: error ORA-01481
Next Topic: ora-01720
Goto Forum:
  


Current Time: Tue Feb 18 01:51:04 CST 2025