How to configure regular expressions on Teradata

Using regular expressions with Teradata

To identify incorrect data, you may want to use Pattern indicators. These indicators use regular expression to work. In Teradata, the regular expression function is only installed by default starting from version 14.

To use the regular expression function in a Teradata version prior to 14, you need to install a User Defined Function (UDF).

Creating a user on Teradata

You must create a Teradata user with CREATE FUNCTION and EXECUTE FUNCTION access rights.

Procedure

Create a user. You can use the following command:

CREATE user test AS password=test perm = 200000000, SPOOL = 100000000;

Grant the user you created at least the CREATE FUNCTION and EXECUTE FUNCTION access rights.

Creating a User Defined Function using a C program

You can create a User Defined Function (UDF) function using a C program and install it on the Teradata database in order to use regular expressions.

Before you begin

Retrieve the Regex_INSTR.c file from the Downloads tab in the left panel of this page.

Procedure

Create a C program like Regex_INSTR.c.
Put this C file on your Teradata database.
Login to Teradata using the user created previously.

Create a UDF function. You can use the following command for example:

CREATE FUNCTION Regex_INSTR (
InputString VARCHAR(4096) CHARACTER SET LATIN,
PatternString VARCHAR(512) CHARACTER SET LATIN )
RETURNS INTEGER
LANGUAGE C
NO SQL
EXTERNAL NAME 'CS!Regex_INSTR!/Regex_INSTR.c!F!Regex_INSTR1'
PARAMETER STYLE SQL;

Test the UDF using the following SQL instruction:
```
select Regex_INSTR('A','[A-Z]');
```
The instruction should return 1.

Editing the pattern indicator and using it in a column analysis

Before being able to use regular expressions on analyzed columns in a database, you must first declare the created regular expression function by adding the SQL instruction and the pattern.

Procedure

In the Profiling perspective, expand Libraries > Indicators > System Indicators.
In the Pattern Matching folder, double-click Regular Expression Matching.
In the Indicator Definition view, click [+].
In the new indicator line, select Teradata from the Database list.
Click Edit... next to the new field to display the Edit expression dialog box.

In the Expression box, add the following SQL expression:

SELECT COUNT(CASE WHEN Regex_INSTR(<%=__COLUMN_NAMES__%>,<%=__PATTERN_EXPR__%>) =1 THEN 1 END), COUNT(*)FROM <%=__TABLE_NAME__%> <%=__WHERE_CLAUSE__%>

Add the modified Pattern indicator into a column analysis.
Run the analysis and switch to the Analysis Results page.
In the Pattern Matching table, right-click the pattern results and select View invalid rows, for example. The SQL editor opens listing invalid data and the SQL expression looks like the following:
```
SELECT * FROM "test"."Employee" WHERE ( not REGEX_INSTR("FULLNAME" , '^[A-Z]+' ) =1 OR "FULLNAME" IS NULL )
```

Using the Pattern Test view

In the Pattern Test View, you can test a string of text against a regular expression.

Procedure

On the menu bar of Talend Studio , click Window > Show View.
In the filter field, start typing Pattern Test View.
Select Pattern Test View and click OK.
Select the DB Connections option then select your Teradata connection from the list.
In the Test Area and Regex fields, enter your test string and your regular expression respectively.
Click the Test button.

Results

A matches or non-matches text is displayed in the view.