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

## 1 - 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.

## 3 - 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 ............... ## 4 - How to add this regular expression in OBIEE ### 4.1 - 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.

### 4.2 - 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 :

dat/obiee/obis/regexp_evaluate.txt · Last modified: 2017/10/23 16:25 by gerardnico