Q:

Mostly numeric range

I have a column in an Oracle database that I am accessing using ColdFusion and want to select a range of items.  
The column is mostly numeric but contains some letters at the end the number.  
Sample data: 55, 56, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 57A, 57, 580, 581, 582, 583, 583, 583, 584, 585, 586, , 587, 588, 589, 58, 59, ....
I want to
select .... where filenumber >= '57' and filenumber <= '59'

and the resutls would return 57, 57A, 58, and 59
The Oracle datatype for the column is a varchar. 

A:

You might be able to use a regular expression to get the digits only, like so:
SELECT ....
WHERE REGEXP_LIKE(filenumber, '^[[:digit:]]+$') >= '57'
AND REGEXP_LIKE(filenumber, '^[[:digit:]]+$') <= '59'

However, I have never used Oracle before, so this might not work. 
Based on this SO answer. 
Another, simplified attempt:
SELECT ....
WHERE REGEXP_LIKE(filenumber, '^[[:digit:]]+$') BETWEEN '57' AND '59'

A:

Got it!   I do have to mention @MrSlayer for pointing me to the fact that Oracle supported Regex (unfortunately, REGEXP_LIKE() returns a boolean result, which isn't completely helpful). 
This can be done like so:
SELECT fileNumber
FROM File_Transfer
WHERE TO_NUMBER(REGEXP_SUBSTR(filenumber, '^(\d+)(\D*)$', 1, 1, 'x', 1)) >= 57
AND TO_NUMBER(REGEXP_SUBSTR(filenumber, '^(\d+)(\D*)$', 1, 1, 'x', 1)) < 60

(I also have a working SQL Fiddle example.) 
This is assuming that you're running under Oracle 11, although you might be able to adapt this to the equivalent in 10 (10 lacks the final parameter, which is what's returning only the numeric portion).   This also assumes that you've got the file numbers as numbers to start with, but this shouldn't be too hard to convert if necessary.   And of course, always prefer ranges in the form 'lower-bound inclusive, upper-bound exclusive' (and avoid BETWEEN like the plague).