Order string field with number and string

SQL++
,
1

Hello,
i’m trying to order a string field that can be a number or a string.
For example the values could be:

"1"
"foo"
"11"
"bar"
"1foo"
"3"
"1 foo"
"2 bar"

and it should be:

"1"
"3"
"11"
"1foo"
"bar"
"foo"
"1 foo"
"2 bar"

therefore the order should be number-string-space.
How can i do that?

Thanks in advance

2

N1QL follows Collation defined here
In your case all are strings. With in strings ASCII character set space , numbers, letters. There is no easy way to get expected results.

3

Ok, i understand.
And there is at least a way to get “2” before “11”? Having for example this behavior https://stackoverflow.com/questions/11808573/sql-order-string-as-number.

4 
INSERT INTO default VALUES (UUID(),{"f1":"1"}), (UUID(),{"f1":"foo"}), (UUID(),{"f1":"11"}), (UUID(),{"f1":"bar"}), (UUID(),{"f1":"1foo"}), (UUID(),{"f1":"3"}), (UUID(),{"f1":"1 foo"}), (UUID(),{"f1":"2 bar"});

SELECT RAW f1
FROM default
WHERE f1 IS NOT NULL
ORDER BY IFMISSINGORNULL(TO_NUMBER(f1),"") ASC, CONTAINS(f1," ") ASC, f1 ASC;

"results": [
    "1",
    "3",
    "11",
    "1foo",
    "bar",
    "foo",
    "1 foo",
    "2 bar"
]
5

if i have:

[
   "foo1",
   "foo2",
   "foo11"
]

it seems not working, because the result is:

[
   "foo1",
   "foo11",
   "foo2"
]

Do you know how to fix it?

6

The output is right. In array you have all strings. “foo11” is before “foo2”

7

yes, there is everything, but the natural and normal order would be “foo2” and then “foo11” because “11” is after “2”.
I don’t know if it’s possible, though.
Like this discussion https://stackoverflow.com/questions/2802341/javascript-natural-sort-of-alphanumerical-strings

8

Natural order of string will be “foo11”, “foo2” in string comparison you compare character by character .
What you are looking is separate string into 2 parts string part, numeric part(convert to number ) and sort on that. Unless you have clear separator it is not possible in N1QL. You can do in application.