[resolved] Patterns (Regular expressions)

[Sebastiao_Qlik]

Talend Open Profiler 1.0.0RC1 is released with a few patterns (in "Libraries" folder). These patterns are regular expressions that databases now support (at least recent versions of MySQL and Oracle). They provide you some kind of business indicators that measure your data quality.
You can drag&drop these patterns can be onto selected columns in the editor. This will automatically create an indicator that will show you how much of your data respects your pattern. By a right click on the "Patterns" folder, you are able to create your own patterns (provided you know how to write regular expressions for your database system).
I think that this topic could be a place where you may share your patterns with others. The patterns you provide could also be integrated in a next release of TOP if they are correct and if we think that they are pertinent enough. But the best way to see your pattern integrated into TOP is to fill a feature request at http://talendforge.org/bugs/my_view_page.php

[ogrolle]

Hello Scorreia,
I tried to use the patterns in on a win32 xp sp3 platform with java 1.6_latest on a oracle 10g (XE and production 10g) database and get the following error: Fail to run this analysis "analysisname". Error message RA-00920: invalid relational operator.
Is this a known bug?
The syntax for the regexp match in Mysql and oracle are different:
Mysql uses: select * from TABLE where columnname1 REGEXP 'regexp-pattern';
Oracle uses: select * from TABLE where REGEXP_LIKE(columnname1,'regexp-pattern');
using the MYSQL syntax in oracle leads to a ORA-00920: invalid relational operator - error.
best regards,
OJ

[Sebastiao_Qlik]

Hi ogrolle,
yes, the syntax is different and the SQL generated by TOP is different. TOP should handle this correctly.
Could you report a bug with that use case, please?

[Sebastiao_Qlik]

this bug has been fixed in 1.1.0 version.

[Anonymous]

And what about SQL Server?
Is it possible to use regexp pattern with SQL Server?

[Sebastiao_Qlik]

Hi pemen,
Currently SQL Server does not provide native support of regular expressions. you must use an external library. See for example http://www.codeproject.com/KB/string/SqlRegEx.aspx or http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
Once this function is implemented, I can help to use it in TOP.
The procedure to add an external function has no user interface yet. This is planed for the next version.