topic Re: SQLServer 2005 and UDF for RegEx in Data Quality

SQLServer 2005 and UDF for RegEx

Anonymous — Sat, 16 Nov 2024 13:22:26 GMT

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 ?

Re: SQLServer 2005 and UDF for RegEx

Sebastiao_Qlik — Wed, 30 Jun 2010 10:16:35 GMT

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.

Re: SQLServer 2005 and UDF for RegEx

Anonymous — Wed, 30 Jun 2010 11:32:15 GMT

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

Re: SQLServer 2005 and UDF for RegEx

Sebastiao_Qlik — Wed, 30 Jun 2010 13:06:50 GMT

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.

Re: SQLServer 2005 and UDF for RegEx

Anonymous — Tue, 07 Dec 2010 10:57:01 GMT

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

Re: SQLServer 2005 and UDF for RegEx

Sebastiao_Qlik — Tue, 07 Dec 2010 14:36:22 GMT

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

Re: SQLServer 2005 and UDF for RegEx

Anonymous — Wed, 08 Dec 2010 09:17:17 GMT

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?

Re: SQLServer 2005 and UDF for RegEx

Sebastiao_Qlik — Wed, 08 Dec 2010 09:39:31 GMT

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

Re: SQLServer 2005 and UDF for RegEx

Anonymous — Wed, 08 Dec 2010 10:53:05 GMT

You are a true legend

Re: SQLServer 2005 and UDF for RegEx

Sebastiao_Qlik — Wed, 08 Dec 2010 10:57:24 GMT

🙂 thank you.

Re: SQLServer 2005 and UDF for RegEx

Anonymous — Wed, 08 Dec 2010 17:29:36 GMT

I agree. Sebastiao is a legend.
One more I'll add --> http://www.regexbuddy.com/ Totally worth the $40