1

I've got an ASP.NET application that's pulling data from an Oracle table into a GridView. I have absolutely no control over the Oracle data, it is what it is (line numbers that indicate parts of kits that go together into a shipping package, I've no idea whose idea it was to use the doubled decimals).

The data looks like this, and the doubled decimal points are the problem (this is a small example of thousands of rows of data):

LINE_NUMBER
1.1
10.1
11.1
2.1
2.1..1
2.1..2
3.1
4.1
5.1
6.1
7.1
8.1
8.1..1
8.1..2
9.1

I need it ordered like this:

LINE_NUMBER
1.1
2.1
2.1..1
2.1..2
3.1
4.1
5.1
6.1
7.1
8.1
8.1..1
8.1..2
9.1
10.1
11.1

I've tried padding the entries with LPAD(LINE_NUMBER, 9, '0'), but I still get a wonky sort due to the doubled periods. See this SO question.

I've tried REGEXP_REPLACE(LINE_NUMBER, '..', ''), to remove the double periods completely, but that didn't work either. I think this is probably where the Oracle answer is going to lie, but I am a complete Oracle noob, although I'm fairly well versed in MySQL / MariaDB & MS SQL.

I've also tried REGEXP_SUBSTR as shown in this site, but I couldn't make his example work for me.

I also tried the technique shown in this SO question, but while it deals with multiple decimal points, it doesn't deal with multiples right next to each other.

I think if I can simply remove the double periods (or maybe just all of the trailing periods after the first one?), I can get the right sort. I can simply SELECT another copy of the same column of data, remove the double periods and ORDER BY that column instead of LINE_NUMBER in the GridView. The double decimal points don't really matter, if they're removed, the string just becomes a floating point number that I can sort numerically (I think).

Unless there's a relatively simple Oracle based way to do this, I'm probably going to manipulate the datatable in the code behind to split the strings on the double periods and figure out how to apply a sort there.

As I said earlier, I have no control over the Oracle data, and I can only query, not write to the database.

In response to questions, here is a list of possible strings. Note that this is not exhaustive, just examples of what's possible. It's also possible (but highly unlikely), that any number could go to three digits:

9.1
41.1
1.1..1
1.1..15
10.1..1
15.1..3
2.1..1
2.1..10
2.1..2
21.1..1
23.1..10
23.1..2
3.1..1
30.1..1
31.1..1
4.1..1
9.1..10
9.1..2

Possible example but unlikely:

123.345..678

The first number is the "ship set" of the package, the second number is the kit in the ship set, and the third number after the double decimals (if it exists) is a line item in a bill of materials. According to my users, the likelihood of any of them going over two digits is small, but not impossible.

delliottg
  • 3,950
  • 3
  • 38
  • 52

1 Answers1

4

Try:

SELECT LINE_NUMBER,
       LINE_NUM
FROM (
  SELECT LINE_NUMBER,
         replace( Line_number, '..', '.' ) As Line_num
  FROM table1
) 
ORDER BY
       to_number(regexp_substr( Line_num, '\d+', 1, 1)) NULLS FIRST,
       to_number(regexp_substr( Line_num, '\d+', 1, 2)) NULLS FIRST,
       to_number(regexp_substr( Line_num, '\d+', 1, 3)) NULLS FIRST,
       to_number(regexp_substr( Line_num, '\d+', 1, 4)) NULLS FIRST
;

Demo: http://sqlfiddle.com/#!4/1786b/11

| LINE_NUMBER | LINE_NUM |
|-------------|----------|
|           1 |        1 |
|         1.1 |      1.1 |
|         2.1 |      2.1 |
|      2.1..1 |    2.1.1 |
|      2.1..2 |    2.1.2 |
|         3.1 |      3.1 |
|         4.1 |      4.1 |
|         5.1 |      5.1 |
|         6.1 |      6.1 |
|         7.1 |      7.1 |
|         8.1 |      8.1 |
|      8.1..1 |    8.1.1 |
|      8.1..2 |    8.1.2 |
|         9.1 |      9.1 |
|        10.1 |     10.1 |
|        11.1 |     11.1 |
krokodilko
  • 35,300
  • 7
  • 55
  • 79
  • 10.1 must be after 9.1 in his desired solution ;) – LauDec Aug 01 '17 at 16:43
  • good :) If he has only one level of this :) don't know if he could have more after or if the example he gave is exhaustive in terms of format – LauDec Aug 01 '17 at 16:47
  • Please see update in the OP regarding format, I think I've answered your question. Please let me know if you guys need more information, and thanks for the help. – delliottg Aug 01 '17 at 16:50
  • @delliottg according to your clarification, This works like a charm. You can even remove the 4th order by – LauDec Aug 01 '17 at 16:57
  • This took a bit of tweaking because my actual query is much more complex, but I've got it sorting properly now, thank you very much! – delliottg Aug 01 '17 at 17:04