Pattern matching in case statement

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Fan

Pattern matching in case statement

Hello,

 

I am new to Teradata Dev.Could you please help me with the below request.

 

CASE WHEN Tablename.ID  LIKE 'pattern matching' THEN 'CNN'

Sample IDs :1-1NRVGXL,1-229HGKM,1-RMKZWA

 

So,how to write pattern matching for this.

 

Thanks in advance.

Raghu

 

 

5 REPLIES
Senior Apprentice

Re: Pattern matching in case statement

What kind of pattern?

There's no explanation what you want to match.

 

If you mean a Regular Expression have a look at REGEXP_SIMILAR

Fan

Re: Pattern matching in case statement

Hi,

 

ID's are of alpha numeric,please see below samples.

1-1NRVGXL,1-229HGKM,1-RMKZWA

I want a like condition for this using wilcard string.

 

Senior Apprentice

Re: Pattern matching in case statement

You still didn't explain your rules for matching.

 

Teradata Employee

Re: Pattern matching in case statement

Just a stab ... but while you give dieter his answer in case it is an involved match, you aren't just asking for how to wildcard, are you?

 

like '%HGKM%'

 

Visitor

Re: Pattern matching in case statement

You can do something  like:

 

CASE 

WHEN Tablename.id LIKE '%cnn%' THEN 'CNN'

ELSE 'UNKNOWN' 

END AS CASE_MATCH 

FROM SAMPLETABLE 

 

Also if you know what position to expect the string you are looking for you can do a substring.