All Forums Database
rajanihara 2 posts Joined 05/14
19 Sep 2014
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

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: http://en.wikipedia.org/wiki/User:Kt_raj1
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.

Dieter

You must sign in to leave a comment.