Returning wrong order in SOQL ORDER BY

Question

I'm facing a problem with the SOQL ORDER BY. I'm quering on a custom object with this query

SELECT completeTreeSequence__c 
FROM CPQ_Line__c 
WHERE CPQ_Header_RU__r.Id = 'a043E000005PfD7QAK' 
ORDER BY completeTreeSequence__c ASC 
LIMIT 10

where completeTreeSequence__c is a text formula field

this is the result:

Results are wrong beacuse the item 5--> "1.1.2.1.10" should be after the item 6 -->"1.1.2.1.1.1".

I think the "10" is interpreted as "1" and this is why it comes with the wrong order.

Does anyone has faced this error and solved it?

Answer 1

This ordering is actually correct because the field type is text. It will be same whichever language (database/server/javascript) you do a sort. Since you have multiple dots, you cannot even set the data type as number. The only option is to get the records and write a custom logic to sort them correctly.

adding based on comments:

Adding 0 still is failing:

var nums = ["01.02.01", "01.02.08", "01.02.011"];
nums.sort(); // 01.02.01, 01.02.011, 01.02.08

Answer 2

I've resolved using a workaround padding every element of the string to 2 digits adding '0' to the left.

now 1.1.1 is sorted before 1.10 as i was expecting