Dynamic Substrings

The challenge was to extract part of an IP address. Simple enough you'd think given that an IP address has a format of 111.222.333.444 (four blocks of 3 digits, seperated by the ".").

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

LOCATE function takes three values
LOCATE(expr1, expr2,int)  

Eg IP address: '111.222.333.444'

The problem - remove the last block of numbers:
The desired result

SUBSTRING('111.222.333.444', 1, INSTR ('111.222.333.444', '.', 1,3) -1)

111.222.333

But what if the blocks of numbers are represented by numbers (i.e. no leading zeros), this wil give us an irregular pattern, with only the decimal point as a marker to indicate the break between sections.

It turns out to be fairly simple. Much as on the Oracle database side when using SUBSTR - a negative number works from the end of the string.

SUBSTRING('111.222.333.444' FROM 1 FOR LOCATE('.','111.222.333.444',-1)-1)

Putting a negative in the locate integer parameter makes it work in reverse.

Some basic string manipulation in OBIEE.

Manipulating strings using SUBSTR

To extract parts of a string of characters like prefixes or file extensions (as an example) we involve a few of the base string functions available in OBIEE.

So what basic features do we need to know about strings, or what basic functionality do we need to manipulate strings.

SUBSTRING

To extract a substring from a string we need to be able to determine several things about the string.
1. Its overall length
2. If a character or sequence of characters is in the string.
3. the start position of a sequence of one or more characters within the string.

So how long is a string?

We can use the LENGTH function to return the number of characters in the string.

We have the LOCATE function to determine the position of a particular string within the string.

Used together they will allow us to extract the second word from the string 'one two three'.

Let's do a worked example

Start a new Analysis and select any column. We will change the contents as we go. Select Edit formula

and edit the column formula to contain 'one two three' (the single quotes are important as they tell OBIEE this is a string).

Add another column and this time we will edit the formula to show the length of the first column. Select the dropdown on the new column and along the bottom of the Column formula panel you will find a column button with a down chevron, click this and select 'one two three'. Highlight 'one two three' then using the f(...) button at the bottom left we will expand the string functions and select LENGTH.

LENGTH('one two three')

Similarly we will now add columns for finding the first occurence of space ' '.

LOCATE(' ', 'one two three')

and another for the next space

LOCATE(' ', 'one two three', LOCATE(' ', 'one two three')+1)
and one to extract the second word, (remember the word starts one character after the first Locate).

SUBSTRING('one two three' FROM LOCATE(' ', 'one two three')+1 FOR (LOCATE(' ', 'one two three', LOCATE(' ', 'one two three')+1)-LOCATE(' ', 'one two three')))

The SUBSTR has the following syntax SUBSTR(string FROM start FOR number). So we can fill in the blanks

• string is obviously 'one two three'
• start is LOCATE(' ', 'one two three')+1
• and number is the length of the word which we can calculate as (the second occurence of space) minus (the first occurence of space + 1)

and finally a test of the length of the second word, just make sure.

LENGTH(SUBSTRING('one two three' FROM LOCATE(' ', 'one two three')+1 FOR (LOCATE(' ', 'one two three', LOCATE(' ', 'one two three')+1)-LOCATE(' ', 'one two three'))))

OBIEE - Using LocateN

So I guess your pulling your hair out about now, LocateN doesn't seem to work.

Quick tip:

Replace the LocateN with Locate, that's right remove the N (but keep the syntax). The Locate function appears to be overloaded to allow this functionality and the LocateN (which doesn't work) seems to be a leftover hanging around.

Locate(' ','one two three')

finds the first space ' ' in the string 'one two three', and here's the hidden part, starting from position 0 (zero).

LocateN(' ','one two three',5)

finds the first space ' ' in the string 'one two three' from position 5.