All Forums Database
rajanihara 2 posts Joined 05/14
19 Sep 2014
How to verify Data Pattern in a Column

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.

Hara Krishna
Raja_KT 1246 posts Joined 07/09
19 Sep 2014

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;

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
20 Sep 2014

Hi Hara,
what's your TD release?
In TD14 there are Regular Expressions which can easily do what you need.


You must sign in to leave a comment.