OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?

Bi Server Architecture With Client

About

Regular expression are really powerfull feature to find pattern through a filter in a string. OBIEE doesn't have a function oustide of the box but permit you to use the regular expression of your database with the evaluate function.

In the example that I will show you, I must extract from a postcode column the two first digit. The two first digit must be only numeric and in my database it's not the case.

When I create a formula function, I have this result :

..........
95
96
97
98
99
BH
BN
EC
KA
LN
.......	

Obviously, some postcode are not good because they start with alphabetic characters.

In this article, I will show you how to suppress this bad row.

The regexp expression

For this article, I work with the Oracle database which had several regular expression function and particularly, the function REGEXP_INSTR which find a pattern and return 0 if the string doesn't match with it.

In my example, I don't want all strings that doesn't start with a number. This rule can be translate with regexp_instr as

REGEXP_INSTR(post_code1, '^[0-9]{2}.*')

You can add more restriction in your pattern and to say that you want the complete postcode pattern as for the Netherlands (minimum 4 numeric and 2 alpha characters):

REGEXP_INSTR(' 2343  BL  ', '^([[::blank]]*[0-9]{4}[[::blank]]*[A-Z]{2}[[::blank]]*)$')

If you execute this function in a sql statement, you will end up with :

  • 1 if the column match with the pattern expression
  • or 0 otherwise.

Example which give the bad rows :

select post_code1, substr(post_code1,0,2) ,  
from 
tab_adrc
where
post_code1 is not null and
REGEXP_INSTR(substr(post_code1,0,2), '[0-9]{2}') = 0

Result :

POST_CODE1	SUBSTR(POST_CODE1,0,2)	REGEXP_INSTR(SUBSTR(POST_CODE1,0,2),'[0-9]{2}')
BH 212UW	BH	0
BH 212UW	BH	0
M15 5RN	        M1	0
M15 5RN	        M1	0
M 15 5RN	M 	0
...............

How to add this regular expression in OBIEE

In the Answer

In Obiee Presentation Service, you can use the OBIEE - Evaluate - Embedded DB Functions in a formula filed and add it as :

EVALUATE('REGEXP_INSTR(%1, ''^([0-9]{2})$'')' as double,LEFT(TAB_ADRC.POST_CODE1, 2))

As you can see, I have changed the logic as I take as expression the first two characters of the postcode with the LEFT function of OBIEE Presentation service.

Obiee Evaluate Regexp Formula Column

You will then end up with a column which give you 1 for good rows and 0 for the bad ones.

Obiee Result Regexp Instr

In the filter

You can then use the column created above to add easily a filter by clicking on the filter icon.

Here below, you will end up with this advanced Sql filter :

Obiee Advanced Filer Regexp Instr Evaluate





Discover More
Column Formula
OBIEE - Criteria Tab

The criteria tab of an answer is a GUI that help you to create the logical SQL. In a column formula, you can add: comment a presentation column a presentation variable a bi server variable...
Bi Server Architecture With Client
OBIEE - Filter

get the Analysis XML, indent it and apply it again of you can try to suppress the



Share this page:
Follow us:
Task Runner