Find patterns in a string (PATINDEX in Sql server equivalent)

Database

Find patterns in a string (PATINDEX in Sql server equivalent)

Hi,

I would like to know any if there is a function to find the pattern in a string, In SQL Server they used PATINDEX function to retrive a 5 char alphanumeric from a string. Below is the data set

Input                            Expected output

All of America A1234   A1234

ABC7896                      C7896

C7412_12                   C7412

BNJ-KKLP-JJ-D3265-87  D3265

ABCDKKK-LL                NULL

In sql server they used SUBSTRING(COlumn, PATINDEX('%[a-z][0-9][0-9][0-9][0-9]%', column), 5).

2 REPLIES
Senior Apprentice

Re: Find patterns in a string (PATINDEX in Sql server equivalent)

What's your Teradata release?

TD14 implements Regular Expressions:

REGEXP_SUBSTR(column, '[a-z][0-9]{4}',1,1,'i')

Re: Find patterns in a string (PATINDEX in Sql server equivalent)

It is TD14 only and this is working fine. Thanks for the response