Sort Varchar field by taking into account groups and subgroups

Asked

Viewed 1,031 times

10

I need to sort the values below in ascending order. They are Varchar type and the solutions I have found so far for Mysql have not worked.

An example of the data type would be 1.12.02, because these values serve as specific code for an account plan table. The problem is that values like 1.2 are coming after 1.15 and so on.

Numbers have up to 3 sublevels after the first point, and each sublevel can have up to 3 characters.

For example, us instead of:

1.1   
1.1.01   
1.10   
1.11   
1.12   
1.12.01   
1.15.1   
1.2

Should be:

1.1    
1.1.01   
1.2   
1.10   
1.11    
1.12   
1.12.01   
1.15.1
mysql sql
  • 1

    To tell you the truth, the biggest problem is misstoring the values. If they have this intended numerical sense, they should store as 10502001 instead of 1.5.2.01, and format on the screen, always remembering to leave the number of digits per drive reserved in a reasonable way. This is very common in systems with chart of accounts, documentation trees, versioning systems etc.

  • Hello ! Exactly, my need is to be done numerical ordering, but I ended up posting all listing because it is very large, the highest values have up to 3 sublevels after the first point, can have up to 3 characters each level.

  • 1

    The dot should be used only on the screen, and not on DB. Normally no formatting is stored in the database. If you need to format, do it in the client language of the system (I imagine you are using one). For example, a CNPJ is usually just a field with digits, no dots and dashes. A plan of accounts only a string of numbers prefixed by zero (usually 2 houses on level 2 and 3 houses or more). Even, this prevents that by mistake there is 1.02 and 1.2 in the base, which would give a bad mess. An ABBCCCDDDEEE format usually resolves.

  • I edited and put an example, please confirm that is according to the desired. Thank you !

1 answer

10


The biggest problem was in the modeling decision, which prioritized the storage of the formatted value, and not the semantic value.


Preferred solution: to remodel

The ideal solution would be to convert the column to some format that had the padding of the preserved boxes, whether string or preferably numeric if the maximum value with padding "fits" in the digits of the chosen type.

Instead of

1.15
1.2.17.01

would store, for example:

01015000000
01002017001

I used the AABBBCCCDDD format (two houses in the first level, and 3 in the following), but this should be adjusted according to your application.

The points would be placed only in the screen display, and removed in the input, with its validation and formatting.


Intermediate solution

A possible solution is the creation of a Function in Mysql string in groups, multiplying their factors by a constant corresponding to the possible levels, effectively ordering.

Another is using substrings, but this involves extracting each group, adding zeros before and cutting to a fixed measure:

SELECT
  conta
FROM
  planodecontas
ORDER BY
  0 + SUBSTRING_INDEX( CONCAT( conta, '.0.0.0' ), '.', 1 ),
  0 + SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT( conta, '.0.0.0' ), '.', 2 ), '.', -1),
  0 + SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT( conta, '.0.0.0' ), '.', 3 ), '.', -1),
  0 + SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT( conta, '.0.0.0' ), '.', 4 ), '.', -1)

The problem in both cases is that they become "ugly and expensive" functions for use in ordination. Doing in the client-application (sort after recover, with the main language of the code), may end up being better.


Solution "oriented to gambiarra":

Actually, it has a function that is made to interpret IPV4 numbers, which are strings with format AAA.BBB.CCC.DDD, which is the INET_ATON

SELECT
   conta
FROM
   planodecontas
ORDER BY
   INET_ATON( SUBSTRING_INDEX( CONCAT( conta, '.0.0.0' ), '.', 4 ) );

The limitation is that the INET_ATON needs fixed number of groups for a balanced comparison, and only works with values from 0 to 255 per group.

To align the groups, we use

SUBSTRING_INDEX( CONCAT( conta, '.0.0.0' ), '.', 4 )

That takes 4 separate groups with . of string concatenated.

I don’t recommend it, but it’s good you know it exists.

  • Great @Bacco inet_aton() will help for now, in the long run I will use your suggestion to remodel the structure. Thanks !

  • Now I understand the purpose @Bacco, I will opt for remodeling. Anyway thanks for the help !

  • @Felipedm added two tested examples that solve, I hope you satisfy in order to close the question. If you have questions let us know. The first example can be easily converted for you to generate a refurbished column if the data is already entered in the database. Just out of curiosity, my first account plan system used binary fields, 1 byte in the first group, and 2 in the following. At that time space was saved in DB :)

  • the intermediary solution worked exactly as I needed, thank you very much for the time willing !!!

  • A simple solution can be to work with masks of type 1.01 1.01.01 1.10 1.11 1.12 1.12.01 1.15.1 1.02 or with columns with "fixed" decimal numbers, keep modeling (bad) but does not require major changes in the scope of the thing ,

  • @Motta even solves, but if this is to be done I think it pays to do the "full service", and ensure the uniqueness of the information with an unambiguous format. I imagine that the work that can be done to ensure this constant format is the same as I mentioned. But really, having constant padding is something. It solves the ordering problem.

  • @Bacco , I already went through a problem the ANS (National Health Agency) defined items like this and it was a problem to order because always fall in the issue of ordering "alpha", as it was an external standard and mandatory by Operators (work in one) the solution was to create a column just to sort , the normal ways would give up chain...

  • @Motta I disgust the IT of the public thing, it’s a festival of horrors. I would have a list of absurd decisions with which I was (and I am still obliged to live) if I would stop to tell.

Show 3 more comments

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.