Cheque Number problem

Question

Hi Experts,

I have a column called check_num (bank check number) as VARCHAR2 type in a payment_line table(Oracle).

The requirement is "I have to search all those checks which numbers are greater than 12345.

Please suggest how can I achieve this?

Answer 1

Hi

I hope the following might help you -

select * from checkTable where TO_NUMBER(check_num) > 12345;

cheers

Answer 2

There most likely is a more elegant solution, but this should do the trick:

SELECT * 
FROM payment_line pl 
WHERE LENGTH(TRIM(TRANSLATE(pl.check_num, '0123456789',' '))) IS NULL 
   AND TRIM(TRANSLATE(pl.check_num, '0123456789','0123456789')) > 12345;

edit:

If I understand your comment to Adam Paynter, for input of:

0A132 
1A117 
2A123 
12D24 
02134 
11111 
12345 
21334

and you used 1A117 as your comparison the resulting set would be:

2A123 
12D24 
02134 
11111 
12345 
21334

Can you confirm that both 02134 and 11111 should be in this result set? They dont seem to meet the requirements of > a value like 1A117. If, however, that was a typo, you can actually run a simple string comparison to get this set:

SELECT * 
FROM payment_line pl
WHERE pl.check_num > '1A117';

edit 2

OK, I think I see where you are going with this. You are looking to get the rows in the db that have been entered after the input row. If you look at my formatted list above, you will see that your result set is everything below your input string. So, with that in mind, i submit for your approval the following:

SELECT * 
FROM payment_line  
WHERE rowid > (select rowid from payment_line where check_num ='1A117');

Answer 3

Unfortunately, Oracle does not provide a handy function such as IS_INTEGER(...), otherwise you could have done a query like:

-- Fictional, though desirable, query:
SELECT *
FROM  checks
WHERE IS_INTEGER(check_num) AND TO_NUMBER(check_num) > 12345

However, there is a way to emulate such a function:

-- Real, though less-than-desirable, query:
SELECT *
FROM  checks
WHERE TRIM(TRANSLATE(check_num, '0123456789', '          ')) IS NULL
  AND TO_NUMBER(check_num) > 12345

The TRANSLATE(check_num, '0123456789', ' ') call replaces each digit within check_num with a space. For example:

 check_num          TRANSLATE(check_num, '0123456789', '          ')
---------------------------------------------------------------------
 '12345'            '     '
 'cat'              'cat'
 '123cat45'         '   cat  '

Therefore, if check_num contains only digits, then TRIM(TRANSLATE(check_num, '0123456789', ' ')) will be NULL (that is, the empty string).

Answer 4

It's tricky to use to_number() in this requirement because forcing Oracle to apply a check that the value is safe to convert to a number before it applies the TO_NUMBER function is not so easy, and an ORA-01722 error might surface in the future.

I think that I would:

SELECT * 
FROM payment_line pl 
WHERE LENGTH(TRIM(TRANSLATE(pl.check_num, '0123456789',' '))) IS NULL 
   AND LPAD(pl.check_num,10,'0') > TO_CHAR(12345,'fm0000000000');

You might ask for the creation of an index on LPAD(pl.check_num,10,'0') to help with this.

Answer 5

If you're on 10g or later you can use regular expressions with an inline view.

The inner select is to get only the numeric check numbers. Then converting and using the where clause is easy. Something like

select * from ( select * from payment_line where regexp_like (check_num,'^[0-9]*$') ) where to_number (check_num) > 12345;

Of course, this only works if you want all numeric check numbers greater than 12345. If you want "numbers" like 1A123 included as well, that's a different story.