0

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.

Aaron
  • 1,042
  • 2
  • 12
  • 30
  • So, specifically, you only want rows where the number is two digits (the length of the 'input'), plus any number of non-digit characters? – Clockwork-Muse Aug 17 '12 at 21:16
  • The number 57 could be any number of digits. Sometimes files split into multiple files with a A,B,C... appended to the file number and I don't want the letters to exclude these files from my range results. – Aaron Aug 17 '12 at 21:26
  • Sorry; what I meant was, you were wanting to only include rows where `fileNumber` had the same number of digits (and was in the given range) as your input criteria. Rows with non-digit data was just 'bonus'. – Clockwork-Muse Aug 17 '12 at 21:29
  • Yes, 570 would be excluded but 57A would not. – Aaron Aug 17 '12 at 21:30

2 Answers2

3

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'
Community
  • 1
  • 1
jeffjenx
  • 17,041
  • 6
  • 57
  • 99
  • Regex is probably your best bet in doing it wit one query. Anything else would require multiple steps and may tend to leave gaps. – Milner Aug 17 '12 at 20:31
  • You might want to use TO_NUMBER to convert the string representation of the numeric into a number before then comparing to your test case. I.e after the regex but before the test. – Ollie Aug 17 '12 at 21:15
  • Apologies, you are using regexp_like rather than regexp_replace. Personally I'd use replace to be left with just the numerics and then TO_NUMBER them. My point about comparing numeric strings is still relevant though. – Ollie Aug 17 '12 at 21:31
2

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).

Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45