All Apps and Add-ons

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

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

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

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

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!