This discussion is archived
9 Replies Latest reply: Mar 7, 2013 9:23 PM by 995602 RSS

Substring instr issue in obiee

833586 Newbie
Currently Being Moderated
Hi,

I want to use the INSTR function in OBIEE

POSITION function found in the forum

INSTR function takes four values:
INSTR (string1, string2, number, number)

Eg IP address
111.222.333.444

The desired result
SUBSTR (IP_ADD, 1, INSTR (IP_ADD, '.', 1,3) -1)
111.222.333

POSITION function, how should I use?

Thanks,
Mino
  • 1. Re: Substring instr issue in obiee
    Deepak Gupta Guru
    Currently Being Moderated
    Mino,

    Here you go, Unfortunately, in this case you can't use OBIEE Position string function as the IP has repeated number of decimals(.) in it. Only function you can use here is LOCATEN. But there is a bug in OBIEE 10g with LOCATEN syntax. So, in order to locate the 3rd '.' position, you will have to use below function:

    LOCATE(expr1, expr2,int) where int is the no. you want the search to start from. For sure, in any case IPs 3rd decimal will start from position 9. So, in this case you will have to hardcode int 9 here.

    So, here is your formula to be used in OBIEE:
    SUBSTRING('111.222.333.444' FROM 1 FOR LOCATE('.', '111.222.333.444', 9)-1)
    OR
    SUBSTRING(IP_ADD FROM 1 FOR LOCATE('.', IP_ADD, 9)-1)

    This will give you 111.222.333

    Hope its useful...
  • 2. Re: Substring instr issue in obiee
    833586 Newbie
    Currently Being Moderated
    Thanks for the answer.

    However, IP number of the information is not uniform.

    0.0.0.0
    001.02.033.3
    03.222.333.343

    IP irregular value.

    any other solution??
  • 3. Re: Substring instr issue in obiee
    833586 Newbie
    Currently Being Moderated
    Description. position function(BMM layer)

    <<expr>> Position (% 1 IN% 2) (<<strExpr>>, <<strExpr>>)

    (<<strExpr>>, <<strExpr>>) What is the meaning?
  • 4. Re: Substring instr issue in obiee
    Deepak Gupta Guru
    Currently Being Moderated
    1st example '0.0.0.0' will def be a problem here. Unfortunately, I don't think any other OBIEE string function could support this.

    I was assuming for min 2 numbers like 00.00.00.00. Do you have any case like the first one in your table ?

    =========
    As I said, you can't use POSITION function here..Just do help for String Function in RPD)

    Position
    Returns the numerical position of the character_expression1 in a character expression. If the character_expression1 is not found, the function returns 0.

    Syntax:

    POSITION(character_expression1 IN character_expression2)

    where:

    character_expression1
    Any expression that evaluates to a character string. Used to search in the second string.

    character_expression2
    Any expression that evaluates to a character string.

    So, these are the 2 expression, In your case its '.' & IP_ADDR.

    =========

    Hope its helpful
  • 5. Re: Substring instr issue in obiee
    598606 Journeyer
    Currently Being Moderated
    830583 wrote:
    any other solution??
    It's really simple
    SUBSTRING('111.222.333.444' FROM 1 FOR LOCATE('.','111.222.333.444',-1)-1)
  • 6. Re: Substring instr issue in obiee
    833586 Newbie
    Currently Being Moderated
    I has solved in the following way.

    SUBSTRING ('111 .222.333.444 'FROM 1 FOR LOCATE ('.', '111 .222.333.444', LOCATE ('.', '111 .222.333.444 ', LOCATE ('.', '111 .222.333.444') + 1) + 1))

    But your suggestion is a better way than my way.

    Thanks AnTonic.
    Mino
  • 7. Re: Substring instr issue in obiee
    Deepak Gupta Guru
    Currently Being Moderated
    Good Learning. Thnx Antonic !!
  • 8. Re: Substring instr issue in obiee
    user8848447 Newbie
    Currently Being Moderated
    This is great solution. Saved my time. Locate N didnt work in 11g. But this solution works!!!!

    Thank You :)
  • 9. Re: Substring instr issue in obiee
    995602 Newbie
    Currently Being Moderated
    if you are facing substring issue in obiee regarding specified string. and u want to string before specified character or words.
    i have one solution for this.

    you want to string before 'g' character
    SUBSTRING("deepak123gupta" FROM 1 FOR LOCATE('g',"deepak123gupta")-1)


    this will display deepak123

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points