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!

New Splunk Observability innovations: Deeper visibility and smarter alerting to ...

You asked, we delivered. Splunk Observability Cloud has several new innovations giving you deeper visibility ...

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...