This content has been marked as final. Show 9 replies
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)
SUBSTRING(IP_ADD FROM 1 FOR LOCATE('.', IP_ADD, 9)-1)
This will give you 111.222.333
Hope its useful...
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)
Returns the numerical position of the character_expression1 in a character expression. If the character_expression1 is not found, the function returns 0.
POSITION(character_expression1 IN character_expression2)
Any expression that evaluates to a character string. Used to search in the second string.
Any expression that evaluates to a character string.
So, these are the 2 expression, In your case its '.' & IP_ADDR.
Hope its helpful