How to verify Data Pattern in a Column

Database

How to verify Data Pattern in a Column

Hi,

In a Select / CASE statement I need to perform a verification on the column data as follows

1. Verify if column A have an 8 Numerics  e.g 99999999 then assign a value

2. Verify if column A have a pattern ZZ999999, first 2 are any alphabets, then assign a different value.

I would like to achieve this in a CASE statement along with other column derivations.

Do help on how to achieve this, Appreciate your help in Advance.

Regards

2 REPLIES

Re: How to verify Data Pattern in a Column

you can try something like this:

select num,case when lower(substr(num,2,2))<>upper(substr(num,2,2)) then 'number has 2 alpha' else

case when character_length(num)=8 then 'number has 8 charcters' end end from your table;

N/A

Re: How to verify Data Pattern in a Column

Hi Hara,

what's your TD release?

In TD14 there are Regular Expressions which can easily do what you need.