Home / Questions / 564158
Accepted
Adrián CL
Asked: 2022-10-26 00:19:46 +0800 CST 2022-10-26 00:19:46 +0800 CST

Sort a String with numeric data and hyphens

  • 772

I have to create a company object in Java with a series of attributes. One of them is the size that comes to me in String and has this format

  • 1-10
  • 11-50
  • 51-200
  • 1000-5000

When making a query in the repository to return a list of companies ordered by size in ascending order, what it does is consider that 1000 goes before 11. I understand that since the second number is smaller than the second number of the second number , is the logic that works.

    @Entity (name="company")
    @Getter
    @Setter
    @ToString

``public class Company {

    @Id
    @Column(name = "id", nullable = false)
    private String id;
    private String website;
    private String name;
    @Digits(integer = 4, fraction = 0)
    private Integer founded;
    private String size;
    private String locality;
    private String region;
    private String country;
    private String industry;
    @Column(name="linkedin_url")
    private String linkedinUrl;

    public Company(String id, String website, String name, Integer founded, String size, String locality, String region, String country, String industry, String linkedinUrl) {
        this.id = id;
        this.website = website;
        this.name = name;
        this.founded = founded;
        this.size = size;
        this.locality = locality;
        this.region = region;
        this.country = country;
        this.industry = industry;
        this.linkedinUrl = linkedinUrl;
    }

    public Company() {

    }
}

        @Query("select c from company c order by c.size")
    List<Company> findByOrderBySizeAsc();
java

2 Answers

  1. Best Answer

    If what you need is to order by the first size of the SIZE, what you need is to obtain that first size as a single element.

    To do this you must:

    • First find the position of the character "-"
    • Second, cut the string to keep the part before that character.

    In the case of SQLServer (for example) the first thing is done with the function CHARINDEXand the second with the SUBSTRING. In your case you do not comment which DB Manager you use, but you should look for the equivalent ones.

    A query like this:

    select c.*, CHARINDEX('-', c.size) as POS, RTRIM(SUBSTRING(c.size, 0, CHARINDEX('-', c.size))) SIZE1 
from company c

    It should return the position of the "-" , and the string before it. Something like this:

    enter image description here

    From there, it's easy to get the aghora sort, just use the field with the "new" value SIZE1 instead of the field SIZE.

    Select DATA.*  from 
(
select c.id, RTRIM(SUBSTRING(c.size, 0, CHARINDEX('-', c.size))) SIZE1 from company c 
) DATA
ORDER BY CAST(DATA.SIZE1 AS INT)

    In the case of MySQL , the search function is LOCATE, and it SUBSTRINGmust start with 1. So you can try the following SQL:

    Select DATA.*  from 
(
select c.id, RTRIM(SUBSTRING(c.size, 1, LOCATE('-', c.size)-1)) SIZE1 from company c 
) DATA
ORDER BY CAST(DATA.SIZE1 AS INT)
    • 0

  2. In the end I had to make a comparator. The code has been as follows:

        public List<Company> findByOrderBySizeAsc() {

    Comparator<Company> bySize =
            (Company c1, Company c2)->
            {
                String sizeC1 = c1.getSize();
                String sizeC2 = c2.getSize();
                String maxSizeC1 = sizeC1.split("[-]|[+]")[0];
                Integer intMaxSizeC1 = Integer.parseInt(maxSizeC1);
                String maxSizeC2 = sizeC2.split("[-]|[+]")[0];
                Integer intMaxSizeC2 = Integer.parseInt(maxSizeC2);
                return intMaxSizeC1.compareTo(intMaxSizeC2);
            };

    List<Company> companyList = companyRepository.findAll().stream().sorted(bySize).toList();

    return companyList;
    • 0