Formula to select all records where 'GROUPDN' field shows a specific pattern?

Database
Enthusiast

Formula to select all records where 'GROUPDN' field shows a specific pattern?

Hello,

I am working with Active Directory data. I am looking to be able to identify those records where the 'GroupDN' field has a pattern of 6 letters followed by 4 numbers.  Something like this:

 

GROUPDNFits Letter/Number Pattern?
CN=INANYC5053_Admin,OU=SecGrp,DC=ad,DC=abcd,DC=comYES
CN=INANYC5062_Admin,OU=SecGrp,DC=ad,DC=abcd,DC=comYES
CN=INANYC5070_Admin,OU=SecGrp,DC=ad,DC=abcd,DC=comYES
CN=InnTEST,OU=FinGrp,DC=ad,DC=abcd,DC=comNO

 

What is the Teradata SQL I can write to identify records where the 'GroupDN' fits the desired pattern above?

Thank you!

 

4 REPLIES
Teradata Employee

Re: Formula to select all records where 'GROUPDN' field shows a specific pattern?

Use the REGEXP functions for pattern matches.

 

REGEXP_SIMILAR(GROUPDN,'[A-Za-z]{6}[0-9]{4}')

 

Enthusiast

Re: Formula to select all records where 'GROUPDN' field shows a specific pattern?

Hi Fred,

 

Your answer almost gets me there - I'm just getting a '2620 - The format or data contains a bad character' error message that I can't quite figure out how to solve.  As long as I don't have a CASE statement, the code you gave me gives me a '0' or '1' answer, and does so correctly.  The error message occurs only after I add the CASE STATEMENT (see example):

 

CASE WHEN (REGEXP_SIMILAR(g.NAME,'[A-Za-z]{6}[0-9]{4}')) <> 0

THEN 'Y' ELSE 'N' END AS IMSI_ServAdminAccess_Cnfrmd

 

I did try a 'CAST', but that does not appear to work, at least the way I'm doing it (see example):

 

CASE WHEN CAST((REGEXP_SIMILAR(g.NAME,'[A-Za-z]{6}[0-9]{4}')) AS CHAR(1)) <> 0

THEN 'Y' ELSE 'N' END AS IMSI_ServAdminAccess_Cnfrmd

Any pointers on how I can get past the error message?

Teradata Employee

Re: Formula to select all records where 'GROUPDN' field shows a specific pattern?

You shouldn't need the CAST; the first example should work.  (The CAST shown doesn't work because you are comparing a CHAR(1) to an integer.)

 

This also works:

select CASE REGEXP_SIMILAR('AbCdEf0123','[A-Za-z]{6}[0-9]{4}')
WHEN 1 THEN 'Y' ELSE 'N' END AS IMSI_ServAdminAccess_Cnfrmd;

 

Perhaps it is g.name that contains a bad character?

Teradata Employee

Re: Formula to select all records where 'GROUPDN' field shows a specific pattern?

The 2620 error specifically indicates a problem in a conversion from character to numeric or vice versa (the problem could be either with the character string or the FORMAT string). But there is nothing in the CASE expression (without the CAST) that would be doing such a conversion. So I don't think it's the CASE statement itself, it's probably some other aspect of your query. For example, trying to insert Y/N into a numeric column would generate this error.