CODEWITHSUNDEEP
sqldatabasesqlite
Remi Sture
Remi Sture

Reputation: 12998

How can I order by number if parts of string?

Is there a way to order by a decimal part of a string? My data is stored something like this: Some string value xx.yy, where xx.yy is a decimal number.

SELECT some_string FROM table ORDER BY some_string desc;

Results in the following:

Some string value 18.9
Some string value 18.8
Some string value 18.7
...
Some string value 18.2
Some string value 18.13
Some string value 18.12
Some string value 18.11
Some string value 18.10
Some string value 18.1

But the desired result is the following:

Some string value 18.13
Some string value 18.12
Some string value 18.11
Some string value 18.10
Some string value 18.9
Some string value 18.8
Some string value 18.7
...
Some string value 18.1

Upvotes: 0

Views: 47

Answers (3)

Remi Sture
Remi Sture

Reputation: 12998

This did the trick. len is apparently not a SQLite function. Thanks, @gordon-linoff.

SELECT some_string 
FROM table_name 
ORDER BY length(some_string) desc, some_string desc

Upvotes: 0

Ravi Varu
Ravi Varu

Reputation: 61

You have to play with the functions.

Please refer to https://www.sqlite.org/lang_corefunc.html

SUBSTR, LENGTH, INSTR are main ones who can save you. You will get to know once you will start exploring them.

or

Still I prefer to separate and store both the values into individual columns. This will provide you more flexibility when it comes to data manipulation.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270463

If the prefix is the same length, you can use this trick:

order by len(some_string) desc, some_string_desc

Upvotes: 2

Related Questions