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 :

  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at del.icio.us
  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at Digg
  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at Ask
  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at Google
  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at StumbleUpon
  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at Technorati
  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at Live Bookmarks
  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at Yahoo! Myweb
  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at Facebook
  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at Yahoo! Bookmarks
  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at Twitter
  • Bookmark "OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?" at myAOL
dat/obiee/obis/regexp_evaluate.txt ยท Last modified: 2017/06/22 12:23 by gerardnico