CODEWITHSUNDEEP
neo4jcypher
BackSlash
BackSlash

Reputation: 22243

Order by numbers last

I have a neo4j database which has nearly 500k CK_ITEM nodes defined as follows:

CK_ITEM: {
    id (String),
    name (String),
    description (String)
}

Suppose we have this sample data:

+--------+----+-----------------+
|  name  | id | description     |
+--------+----+-----------------+
| Mark   | 1  | A lot of things |
| Gerald | 9  | Coff2e          |
| Carl   | 2  | 1 mango         |
| James  | 3  | 5 lemons        |
| Edward | 4  | Coffee          |
+--------+----+-----------------+

I need to order the data by description ASC. This is my query:

MATCH (n:CK_ITEM)
ORDER BY
    n.description ASC

This results in:

+--------+----+-----------------+
|  name  | id | description     |
+--------+----+-----------------+
| Carl   | 2  | 1 mango         | <-- '1' < '5'
| James  | 3  | 5 lemons        | <-- '5' < 'A'
| Mark   | 1  | A lot of things | <-- 'A' < 'C'
| Gerald | 9  | Coff2e          | <-- '2' < 'e'
| Edward | 4  | Coffee          | 
+--------+----+-----------------+

Now, the customer asked me to order the results so that they are still in ascending order, but numbers are left last.

Basically he wants the results to be:

+--------+----+-----------------+
|  name  | id | description     |
+--------+----+-----------------+
| Mark   | 1  | A lot of things |
| Edward | 4  | Coffee          |
| Gerald | 9  | Coff2e          | <-- Coff2e after Coffee
| Carl   | 2  | 1 mango         | <-- 1 and 5 ASC after everything
| James  | 3  | 5 lemons        |
+--------+----+-----------------+

Translated to a pseudo-query, it would be something like this:

MATCH CK_ITEM ORDER BY letters(description) ASC numbers(description) ASC

Is it possible to have this kind of sorting (letters first ascending, numbers last ascending) in a single query? How?

Upvotes: 2

Views: 260

Answers (3)

cybersam
cybersam

Reputation: 67044

The following is a Cypher query that will perform a sort where digits come last (at every character position).

NOTE: THIS APPROACH IS NOT EFFICIENT, but is presented as an example of how to do this in Cypher if you absolutely needed to.

The query splits every description value into single-character strings, tests each character to see if it is a digit, constructs a new string (character by character) -- replacing every digit with a corresponding UTF-16 character in the hex range FFF6 to FFFF (these are the highest possible UTF-16 character encodings, and your raw data is unlikely to already be using them), and uses that new string for sorting purposes.

WITH {`0`:'\uFFF6',`1`:'\uFFF7',`2`:'\uFFF8',`3`:'\uFFF9',`4`:'\uFFFA',`5`:'\uFFFB',`6`:'\uFFFC',`7`:'\uFFFD',`8`:'\uFFFE',`9`:'\uFFFF'} AS big
MATCH (n:CK_ITEM)
WITH n, SPLIT(n.description, '') AS chars, big
RETURN n
ORDER BY
  REDUCE(s='', i IN RANGE(0, LENGTH(chars)-1) |
    CASE WHEN '9' >= chars[i] >= '0'
    THEN s + big[chars[i]]
    ELSE s + chars[i]
    END)

Upvotes: 1

stdob--
stdob--

Reputation: 29167

You can use reqular expression and UNION:

MATCH (n:CK_ITEM) WHERE NOT n.description =~ '[0-9].*'
RETURN n
ORDER BY
    n.description ASC

UNION

MATCH (n:CK_ITEM) WHERE n.description =~ '[0-9].*'
RETURN n
ORDER BY
    n.description ASC

Upvotes: 0

Bruno Peres
Bruno Peres

Reputation: 16373

You can order this way:

MATCH (n:CK_ITEM)
   RETURN n
ORDER BY
    substring(n.description,0,1) in ['0','1','2','3','4','5','6','7','8','9'], n.name

Upvotes: 0

Related Questions