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

[asagban]

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)

[Sebastian2 · Accepted solution]

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]

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

[davebrooking]

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