Percentage sorting of databases (including string interception, specified character lookup, type conversion)

Time:2019-6-30

Demand:

Include a column of `rate'as a percentage in the profit table, the data type is varchar, and the inserted data is percentage (xx.xx%). Require sorting by percentage size, the database is oracle.

Analysis:

Taking ascending order as an example, the rate field in this requirement is of Varcahr type, so the direct ordering will be "1%-> 10%-> 11%-> 2%-> 3%", which is obviously not the result we want.
So we need to convert the data type to the number type when we query the column data. In oracle, we provide two ways to convert the string type to the number type:
   1. TO_NUMBER (COLUMN) converts a column to a digital type.
          2. CAST (COLUMN AS NUMBER (10,2)) converts a column to another type, where it is a number, so long as the condition is satisfied, it can be converted to any other type.
However, after testing, it is not feasible to sort directly after data type conversion, and error will be reported: the column is not a valid number, what is the reason? Because "%" and "xx%" are not valid numbers in the rate column and cannot be directly converted to number type.
At this point, we only need to remove the "%" at the end to get a number that can be converted to number type, which means we need to intercept the string, the data before "%" is intercepted. In oracle, a substr method is provided to intercept strings:
   Grammar: 
      substr( string, start_position, [ length ] )
      String is a string, start_position is the starting position, and length is the interception length.
      Where start_position is negative, the starting position starts at the end of the string. Length can be omitted, and when length is omitted, all the characters after the start_position position position are returned.
Seeing this, we find that the substr method can only be intercepted backward, but not backward-forward. In addition, we need all the characters before "%", but the length is unknown, so we use another method instr, which returns the location of the specified character. Namely, the specified character lookup:
    Grammar:
        instr(string1,string2[,start_position[,nth_appearence]])
        String1: Find it in this string.
        String2: The string to look up in string1.
        Start_position: The location to look up from string1. Optional, default is 1, positive, left to right search, negative, right to left search. (Here it's the same as substr, that is, when it's negative, it's just starting from the right.)
        Nth_appearence: The number of string2 occurrences in the lookup. Optional, default 1, not negative.
In this way, we can use instr to find the position where "%" appears, that is, the last character position index of the column data, and subtract 1 from the index to get the character length of the column data after removing the percentile sign. That is, we need to intercept the length of the string. The intercepted string is substr (rate, 0, instr (rate,'%') - 1). Then turn it into number type and sort it into OK!
Finally, we get the SQL statement:
    Select * from profit order by CAST (substr (rate, 0, instr (rate,'%') - 1) AS number (10, 2)) or
    select * from profit  order by   to_number(substr(rate  , 0,instr(rate  ,'%')-1))

Summary:

In actual development, such a situation is relatively rare, because "%" can be added directly to the front end, the database only needs to store the number type of data. But there are other similar situations, such as: the employee number of a company: serial number + M, serial number + MX.... There are similar situations.
Additionally, it's simpler if the letters are in front, because you just need to start from the last letter, and intercept everything behind the letters to convert the type sort directly.