topic Re: Issue on Analyzed Column in MS SQL Server Connection in Data Quality

Issue on Analyzed Column in MS SQL Server Connection

_AnonymousUser — Tue, 06 Aug 2013 15:31:41 GMT

When I am trying to configure a Column Analysis on a email_address column from MS SQL Server connection and while trying to check Patterns > Regex > Internet nodes > Email Address pattern.
I am getting a message that expression is not available for MS SQL Server . Do you want to add ?
If I click yes it is giving me pattern for.....
Oracle : '^+@+\.{2,4}$'
MySQL : '^+@+\\.{2,4}$'
Java : '^+@+\.{2,4}$'
And a "+" button . I think it is for adding a pattern. But what will be the pattern for SQL Server.
There is some difference between Oracle and MySQL. So there may have some difference in SQL Server. Can somebody help me for SQL Server pattern ?

Re: Issue on Analyzed Column in MS SQL Server Connection

Sebastiao_Qlik — Thu, 22 Aug 2013 15:51:55 GMT

Hi,

regular expression are not natively supported by SQL Server.

You first need to make sure that your server supports regular expression. If not, then have a look at How to deploy the regular expression function to the SQL server

Then you need to adapt the indicator template to the SQL server function. This means that you must add the SQL template for SQL Server in the indicator: Libraries/Indicators/System Indicators/Pattern Matching/Regular Expression Matching

And finally, you need to add the regular expression in the Pattern (you may copy the Oracle one).

Otherwise, the simplest way is the use the java engine in the analysis so that your data are first downloaded and then analyzed (in case you have a lot of data, it's not advised to do that though). By using the java engine, you don't depend on the DBMS server.

Re: Issue on Analyzed Column in MS SQL Server Connection

Sebastiao_Qlik — Thu, 22 Aug 2013 15:53:30 GMT

You may also find some information in the studio:
In the menu Help>View bookmarks
Then click on "SQL Server 2005 regular expressions".

Re: Issue on Analyzed Column in MS SQL Server Connection

Sebastiao_Qlik — Thu, 22 Aug 2013 15:55:42 GMT

and more details in https://help.talend.com/pages/viewpage.action?pageId=13806377