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.
Articles Related
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.
You will then end up with a column which give you 1 for good rows and 0 for the bad ones.
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 :
