All Apps and Add-ons

What is the equivalent of ORACLE SQL 'instr ' in SPLUNK?

asagban
Engager

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)
0 Karma
1 Solution

Sebastian2
Path Finder

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}.*"

View solution in original post

Sebastian2
Path Finder

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}.*"

jplumsdaine22
Influencer

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

0 Karma

davebrooking
Contributor

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...