SQLServer 2005 and UDF for RegEx

[Anonymous]

Hello,
I'm looking for clarification with SQLServer RegEx usage inside TOP 4.0.1.
I've got an assembly working, udf functions created and working - but nowhere to put them in TOP.
Adding a RegExp analysis, there's a popup which tells you to go to a removed option page.
And the documentation tells you to go to the page 281 which, too, doesn't exists any longer :-).
There's a way to duplicate any RegExp to personalized indicators, but it's very painful - and only make count analysis.
It'll be disappointing there's no solution to "quickly" use regexp anymore.
Can you tell me what did I miss, please ?

[Sebastiao_Qlik]

Hi Olivier,
browse to the "Libraries>Indicators>System>Pattern Matching" folder and open the "regular expression matching" indicator with a double click.
In the editor, in the "indicator definition" section, add the appropriate SQL template query which uses your regex function.
you can copy the template of Oracle and adapt it to use your function.
See the screen capture.
Then, you may need to edit the patterns that you want to use in the analyses (at least to add the SQL server database when there is no default pattern available. This is the case for the email pattern for example where there are pattern definitions only for Oracle and MySQL. You just need to copy a definition and select SQL Server in the pattern editor. Be careful with the \ which may need to be escaped (it's the case with MySQL).
You can test your regex to make sure it works as you expect with the "test" button in the pattern editor.

[Anonymous]

Hi scorreia,
I alredy tested this, but using a regexp in a analysis with SQLServer still gives me a popup about the RegExp limitation in SQLServer. . Then I ve got another message with Error {0} The indicator is already positionned on this column . But no indicator is really created

Olivier

[Sebastiao_Qlik]

I suggest that you open an issue in our bugtracker: http://www.talendforge.org/bugs/bug_report_advanced_page.php
attach a screen capture of the message and attach the error log http://www.talendforge.org/wiki/doku.php?id=bugtracker#attach_the_error_log_to_the_bugtracker
A developer will investigate this in further details.

[Anonymous]

Hey @ OlivierR
Could you find a workaround for this issue as I am facing it myself? I do not have the UDF option in the Preferences and need this function quite urgently.
Thank you

[Sebastiao_Qlik]

Hi Pinback,
the preference page is not used anymore in recent versions of the profiler.
What you need to do is to edit the regex indicator called "Regular Expression Matching" in "Libraries/Indicators/System Indicators/Pattern Matching/" and check that the patterns you want to use contain a valid expression for MS SQL.
Let me know if this does not work for you.
thanks for your bugtrack issue: 17457

[Anonymous]

Thank you very much for the speedy reply... A last request -> As i am new to MSSql, have you maybe got a good link for me to learn patterns on MSSql, I have no idea where to begin?

[Sebastiao_Qlik]

in the studio, click on the menu "help > bookmarks" then you'll find a link related to SQL Server.
But if you want to learn regex, have a look at sites like regexlib.com.
Some of my bookmarks are available at http://www.delicious.com/scorreia_pro/regex+reference

[Anonymous]

You are a true legend

[Sebastiao_Qlik]

🙂 thank you.