1

I have a column call it id:

1
1.1
1.2
1.2.1
1.2.2
1.19.1.1
1.2.3.1
1.2.3.2
1.19.1
1.19.1.2

... etc... What I'd like to do is include an ORDER BY statement that splits the string, to become like this:

1
1.1
1.2
1.2.1
1.2.2
1.2.3.1
1.2.3.2
1.19.1
1.19.1.1
1.19.1.2

... etc.... How would I do something like this?

I tried this solution Order By Split Column but doesn't work when length of number are doesn't same

Great Reward
  • 25
  • 7
  • 1
    Have you tried [Gustav's answer](http://stackoverflow.com/a/28937678/3820271)? `Split` is the way to go here. – Andre Jun 16 '16 at 06:27
  • What does "doesn't work" mean, please? The function returns 0 (zero) for those parts not present to preserve the _Order By_. – Gustav Jun 16 '16 at 07:39

2 Answers2

2

I tried this solution Order By Split Column but doesn't work when length of number are doesn't same

You can make them contain the same number of "." or tokens. For example, if you know that there can be a maximum of 4 dots (eg. 1.1.1.1.1) then you can run this script to concatenate the remaining ".0" tokens :

create table mytable(id varchar);

insert into mytable(id)
values  ('1'), ('1.1'), ('1.2'), ('1.2.1'), ('1.2.2'), ('1.19.1.1'), ('1.2.3.1'), ('1.2.3.2'), ('1.19.1'), ('1.19.1.2');

select id as original, id||
  case when length(id) - length(replace(id,'.','')) = 0 then ".0.0.0.0"
       when length(id) - length(replace(id,'.','')) = 1 then ".0.0.0"
       when length(id) - length(replace(id,'.','')) = 2 then ".0.0" 
       when length(id) - length(replace(id,'.','')) = 3 then ".0" 
  end as computed
from mytable;

You can run (each sql command at a time) the script here to test it

Result : 

----------------------------
| original   |  computed   |
----------------------------
| 1          |  1.0.0.0.0  |
| 1.1        |  1.1.0.0.0  |
| 1.2        |  1.2.0.0.0  |
| 1.2.1      |  1.2.1.0.0  |
| 1.2.2      |  1.2.2.0.0  |
| 1.19.1.1   |  1.19.1.1.0 |
| 1.2.3.1    |  1.2.3.1.0  |
| 1.2.3.2    |  1.2.3.2.0  |
| 1.19.1     |  1.19.1.0.0 |
| 1.19.1.2   |  1.19.1.2.0 |
----------------------------

After this conversion you can apply the script that you mentioned.

Sorin J
  • 542
  • 1
  • 4
  • 14
  • Sadly, replace doesn't work in MS-access accessed by VB.NET, does anybody give explanation use INSTR like this http://stackoverflow.com/questions/4834536/exception-when-trying-to-execute-replace-against-ms-access ..? – Great Reward Jun 18 '16 at 02:54
2

As I understand, you need solution for MS Access SQL, not MS SQL. If so, then Gustav's solution from topic you mentioned works fine without any change

Community
  • 1
  • 1
Sergey S.
  • 6,296
  • 1
  • 14
  • 29