If an account number entered does not fit the format required by the company..

[cogtm1]

I have the below requirement.
If an account number entered does not fit the format required by the company

Bank        Account Number
A-   9 Alphanumeric Digits (No Symbols)
B-   9 Numeric Digits (No Symbols)
C-   9 Numeric Digits (No Symbols)
D-   9 Numeric Digits (No Symbols)
E-   8 Alphanumeric Digits (No Symbols)
F-   9 Alphanumeric Digits (No Symbols)
G-   9 Alphanumeric Digits (No Symbols, Starts with "P")
H-   8 Numeric Digits (No Symbols)
I-   8 Numeric Digits (No Symbols)
J-   8 Numeric Digits (No Symbols)
K-   8 Numeric Digits (No Symbols)
L-   2 Digit Alphanumeric Plus Space Plus 5 Digit Numeric
M-   9 Numeric Digits (No Symbols)
N-   Starts with "H" Followed by 5 or 6 Numeric Digits
O-   9 Numeric Digits (Hyphen Between First 4 and Last 4)
P-   8 Numeric Digits (No Symbols)

I need to create this report in Cognos Report studio, can somebody give some idea on the filter condition. Thanks

[CognosPaul]

Which version of Cognos? What RDBMS and where do you need to do the validation? Is this supposed to return a list of values that are already in the database, or pop up a warning as the values are being entered into a prompt?

How much control do you have over the database? Can you create or modify a table if needed?

[cogtm1]

Which version of Cognos?  Cognos 8.4
What RDBMS?  DB2 UDB
where do you need to do the validation? Cognos
Is this supposed to return a list of values that are already in the database? Yes
Can you create or modify a table if needed? Yes

Thanks for the reply.

[CognosPaul]

So you need to render a list of account numbers that don't match the associated company format.

DB2 is a bit difficult, as it doesn't have native regex support. You need to set up the regex udfs. See here: http://www.ibm.com/developerworks/data/library/techarticle/0301stolze/0301stolze.html

Once you have that set up, modify your banks table to add a new regex field that contains the search string. So bank a would be ^[0-9aA-zZ]{9}$ and bank G is ^[P][0-9aA-zZ]{8}$