Find jobs that match a patern of numbers and letters

[aarondewberry]

All

I am trying to write a query in the query panel to pull back jobs where the bill account number starts with two numbers then two letters then two numbers.
This combination of numbers and letters can vary but I need only the accounts with that combination eg. 22ab56
Are there any wildcards I can use?

Thanks

 

[blom0344]

Please state the database you are using and we will work out a nice expression...

Ties Blom

 

[aarondewberry]

I pull the bill account number from the accounts database

Thanks

 

[blom0344]

Oracle,DB2,SQl server ??

Ties Blom

 

[aarondewberry]

Oh sorry. Oracle

Thanks

 

[blom0344]

I think this a little beyond using wildcard expressions.
With a little creativity you can use the ORACLE function ASCII to test each position in the string:

position1:

ASCII(SUBSTR(ACCOUNT_NUMBER,1,1))

For a value in the range 0-9 the ASCII value ranges between 48 - 57.

So, create 6 universe objects for the 6 positions in the string.

The resulting expression will be:

ASCII(SUBSTR(ACCOUNT_NUMBER,1,1)) BETWEEN 48 AND 57 
AND
ASCII(SUBSTR(ACCOUNT_NUMBER,2,1)) BETWEEN 48 AND 57  
AND
ASCII(SUBSTR(ACCOUNT_NUMBER,3,1))NOT BETWEEN 48 AND 57 
AND
ASCII(SUBSTR(ACCOUNT_NUMBER,4,1))NOT BETWEEN 48 AND 57 
AND
ASCII(SUBSTR(ACCOUNT_NUMBER,5,1)) BETWEEN 48 AND 57 
AND
ASCII(SUBSTR(ACCOUNT_NUMBER,6,1)) BETWEEN 48 AND 57

Or you can define this expression as a filter object in the universe..

Ties Blom