Order Specific Character in SQL server

Question

I use mariadb, this code for the table CREATE TABLE a( version_id varchar(10) );

I got the following entry from table a in my database:

10.1.
10.10.
10.11.
10.12.
10.2.
10.3.
10.4.
10.5.
10.6.
10.7.
10.8.
10.9.

I want to order the entry so I get:

10.1.
10.2.
10.3.
10.4.
10.5.
10.6.
10.7.
10.8.
10.9.
10.10.
10.11.
10.12.

How can I solve it?

What actual SQL have you tried that you can actually post IN the question that we can help you with? Please also post code that shows your table(s) and columns (with the types) so we can properly help you here. — Mark Schultheiss, Apr 11 '23 at 16:01
@MarkSchultheiss I am sorry for that, I use mariadb, this code for the table CREATE TABLE a( version_id varchar(10) ); — Afnil Dwi Oktanto, Apr 11 '23 at 16:17
I've reopened this question because it is [about MariaDB](https://stackoverflow.com/questions/75987803/order-specific-character-in-sql-server?noredirect=1#comment134021512_75987803) *not* Oracle (and the tag was amended to SQL Server?), neither of which were *ever* tagged. As a non-SME (of MariaDB) I don't want to reclose this as a dupe of a different question, but I'm sure a gold badger can find one. — Thom A, Apr 11 '23 at 16:21
I have voted to once again close this since there is no attempted SQL in the question. Good update to add the table however there!. — Mark Schultheiss, Apr 13 '23 at 21:58

score 0 · Answer 1 · answered Apr 13 '23 at 15:10

0

We can remove the trailing point and convert the value to double using TRIM:

select *
from mytable
order by TRIM("." FROM version_id) + 0

answered Apr 13 '23 at 15:10

SelVazi

SelVazi · Accepted Answer · 2023-04-11T16:39:41.153

-3

You can sort by the number extracted from your string:

select *
from mytable
order by CAST(REPLACE(version_id, '.', '') AS INT)

This will handle mixed versions (10.*, 20.* ... ) :

select *
from mytable
order by CAST(REPLACE(version_id, '.', '') + REPLICATE('0', 5-LEN(REPLACE(version_id, '.', ''))) AS INT)

edited Apr 11 '23 at 16:39

answered Apr 11 '23 at 15:58

SelVazi

1

This does not really answer the question since the OP posted nothing related to/about the database structure or any attempt and thus this is likely just a wild guess which may or may NOT resolve the issue. Please don't encourage poorly asked questions with answers without more information from the OP. – Mark Schultheiss Apr 11 '23 at 16:04
1

Thanks very much, It works for me, I thought it might be the ORDER BY CASE but have never used it before and the other stuff I read on it didn't seem to fit exactly. Awesome! – Afnil Dwi Oktanto Apr 11 '23 at 16:18
2

If you add a value like `'20.2.'` then this is likely going to produce wrong results too. `'202'` is less than `'1010'` – Thom A Apr 11 '23 at 16:25
Good remark @ThomA, I have made it more general https://dbfiddle.uk/lDfwsfVI – SelVazi Apr 11 '23 at 16:40
Will this really work on Mariadb? – jarlh Apr 11 '23 at 18:54
@jarlh No, this is Sql server, its mentioned in the title, someone have changed the tag to mariadb – SelVazi Apr 11 '23 at 19:48
Why don't remove just the trailing point and convert the value to double ? `ORDER BY TRIM("." FROM version_id) + 0` – Georg Richter Apr 12 '23 at 06:16

2 Answers2