Are you a member of the Splunk Community?
- Find Answers
- :
- Apps & Add-ons
- :
- All Apps and Add-ons
- :
- Re: What is the equivalent of ORACLE SQL 'instr ' ...
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am trying to substring a text depending on the varying positions of the "_"
The query in SQL =
CASE
WHEN SUBSTR(DSLAM_NAME,1,instr(DSLAM_NAME,'_')-1) IS NULL THEN SUBSTR(DSLAM_NAME,1,instr(DSLAM_NAME,'-')-1)
ELSE
SUBSTR(DSLAM_NAME,1,instr(DSLAM_NAME,'_')-1)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Regular Expressions are what you are looking for. For instance by using rex :
(I'm not sure what exactly you are trying. Looks like you want to skip the first char and everything until an "-" if there is one or the rest of the string if there is no "-"). Warning: no look all in (Regex is untested); however, something like this should do it:
... | rex ".(?P<extracted_field_name>.*?)[-]{0,1}.*"
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Regular Expressions are what you are looking for. For instance by using rex :
(I'm not sure what exactly you are trying. Looks like you want to skip the first char and everything until an "-" if there is one or the rest of the string if there is no "-"). Warning: no look all in (Regex is untested); however, something like this should do it:
... | rex ".(?P<extracted_field_name>.*?)[-]{0,1}.*"
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Also try the field extractor if you're not sure how to write the regex - http://docs.splunk.com/Documentation/Splunk/6.3.2/Knowledge/ExtractfieldsinteractivelywithIFX
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I agree I would use regular expressions to mimic the Oracle instr function, but I think it is also possible with a fairly ugly eval statement
... |eval fieldname=(if(mvcount(split(DSLAM_NAME,"_")>1,mvindex(split(DSLAM_NAME,"_"),0),mvindex(split(DSLAM_NAME,"-"),0))
Dave
Leveraging Automated Threat Analysis Across the Splunk Ecosystem
What Is Splunk? Here’s What You Can Do with Splunk
Level Up Your .conf25: Splunk Arcade Comes to Boston
