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.