topic Re: What is the equivalent of ORACLE SQL 'instr ' in SPLUNK? in All Apps and Add-ons

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

asagban — Tue, 22 Dec 2015 02:01:35 GMT

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)

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

Sebastian2 — Tue, 22 Dec 2015 07:16:17 GMT

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

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

jplumsdaine22 — Tue, 22 Dec 2015 10:30:51 GMT

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

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

davebrooking — Wed, 23 Dec 2015 12:12:39 GMT

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