Sql: Order by Using a Substring Within a Specific Column... Possible

SQL: ORDER BY using a substring within a specific column... possible?

You can put a CASE statement in the ORDER BY to accomplish this. A better route would be to change the application and table to actually store this relevant data in columns where it belongs when you have the development time to do that.

ORDER BY
    CAST(SUBSTRING(issue, 1, 4) AS INT) DESC,  -- Year
    CASE
        WHEN issue LIKE '%First_Quarter' OR issue LIKE '%Winter' THEN 1
        WHEN issue LIKE '%Second_Quarter' OR issue LIKE '%Spring' THEN 2
        WHEN issue LIKE '%Third_Quarter' OR issue LIKE '%Summer' THEN 3
        WHEN issue LIKE '%Fourth_Quarter' OR issue LIKE '%Fall' THEN 4
    END

Order the seasons however you want. You could also order them in a specific way (Q1 followed by Spring, followed by Q2, etc.) by adjusting the CASE statement.

SQL order by part of substring

You'd have to formally define via regexp what exactly is "bold" or "italic".

If you assume that the first group of digits is the first variable to order on and the second is the second:

ORDER BY substring(col,'\d+')::int, substring(col,'\d+[^\d]+(\d+)')::int;

Or alternatively, if your definition is that the first number is the digits following the first space and the second is the ones following a dash:

ORDER BY substring(col,' (\d+)')::int, substring(col,'-(\d+)')::int;

Of course you should first debug these buy running select substring(...).

SQL Order By Specific String in a Column

You can try something like this:

Example data:

select * from test;
+--------+----------------------------------------+
| pid    | picture                                |
+--------+----------------------------------------+
| 169799 | 2017/March/18/169799_4_iMbw7.jpg       |
| 169799 | 2017/February/18/169799_2_jadsflkjasdf |
| 169799 | 2017/June/06/169799_1_jasfd;ads        |
| 169799 | 2017/May/18/169799_5_jasfd;ads         |
| 169799 | 2017/June/12/169799_10_jasfd;ads       |
| 169799 | 2017/January/12/169799_3_iMbw7.jpg     |
+--------+----------------------------------------+

Sort by date

select * from test order by str_to_date(substring_index(picture,'/',3), '%Y/%M/%d') desc;
+--------+----------------------------------------+
| pid    | picture                                |
+--------+----------------------------------------+
| 169799 | 2017/June/12/169799_10_jasfd;ads       |
| 169799 | 2017/June/06/169799_1_jasfd;ads        |
| 169799 | 2017/May/18/169799_5_jasfd;ads         |
| 169799 | 2017/March/18/169799_4_iMbw7.jpg       |
| 169799 | 2017/February/18/169799_2_jadsflkjasdf |
| 169799 | 2017/January/12/169799_3_iMbw7.jpg     |
+--------+----------------------------------------+

Explanation:

• substring_index(picture,'/',3) will split text by / and output the first 3 item
• str_to_date(..., '%Y/%M/%d') converts the result to a date based on the given format. %M is month name

EDIT

Sort by number

select * from test
order by cast(
  substring_index(substring_index(picture, '_', 2), '_', -1)
  as unsigned
);
+--------+----------------------------------------+
| pid    | picture                                |
+--------+----------------------------------------+
| 169799 | 2017/June/06/169799_1_jasfd;ads        |
| 169799 | 2017/February/18/169799_2_jadsflkjasdf |
| 169799 | 2017/January/12/169799_3_iMbw7.jpg     |
| 169799 | 2017/March/18/169799_4_iMbw7.jpg       |
| 169799 | 2017/May/18/169799_5_jasfd;ads         |
| 169799 | 2017/June/12/169799_10_jasfd;ads       |
+--------+----------------------------------------+

Explanation:

• substring_index(picture, '_', 2) will split text by _ and output the first 2 item
• substring_index(..., '_', -1) picks the last item, which is the number

References:

• https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index
• https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date
• https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

How to order by certain part of a string?

You should be able to just extract substring from your code.

order by SUBSTRING(codes, 4) asc

SQL Server query order by column containing string

To do it in SQL you could use

SELECT *
FROM   myTable
ORDER  BY CASE
            WHEN mycolumn LIKE '%XYZ%' THEN 0
            ELSE 1
          END,
          mycolumn 

SQL Server SELECT query ordering by substring

This should do it .. (order first by the first two chars, and then by the last char (assuming that the final n is always one digit long))

SELECT
   Column1
FROM
   TABLENAME
ORDER BY
   LEFT(Column1,2) ASC, 
   RIGHT(Column1,1) ASC

MSSQL - does a CASE statement in an ORDER BY clause return column ordinals?

The only time an item in a ORDER BY clause is considered to be an ordinal is when its an integer literal, looking at the sql-92 standard we can see the origin of this:

If a <sort specification> contains an <unsigned integer>, then the
<unsigned integer> shall be greater than 0 and not greater than the
degree of T. The <sort specification> identifies the column of T with
the ordinal position specified by the <unsigned integer>.

If the ORDER BY clause contains a non-constant scalar expression such as CASE then the result of that expression is used as the sort key, i.e. if it evaluates a row in the result set and returns an integer n it is the value of n that is sorted upon.

ORDER BY in SQL where column is synthetic string with embedded integer

Fixed width rep and it uses only functions available in both H2 (not tagged) and SQLS (tagged):

SELECT 
    CONCAT(
      CAST(name as CHAR(10)), --right pad to 10, 

      YEAR(date_of_entry),
      RIGHT(CONCAT('0',MONTH(date_of_entry)),2),
      RIGHT(CONCAT('0',DAY(date_of_entry)),2), --yyyymmdd

      CAST(flag1 as CHAR(1)), --rpad to 1, doesn't need cast if never null/0 length

      CAST(flag2 as CHAR(1)), --maybe doesn't need cast, see above

      RIGHT(CONCAT('0000000000', CAST(salary AS INT)),10), --lpad with 0 to 10 wide

      CAST(flag3 as CHAR(1)), --maybe doesn't need cast, see above

      RIGHT(CONCAT('0000000000', id), 10) --lpad with 0 to 10 wide

    ) AS SYNTHETIC_ORDER
FROM 
    TEST
ORDER BY 
    SYNTHETIC_ORDER DESC

Points of note:

• Your CREATE TABLE statement doesn't mention ID, but your query does; included ID

• Your query doesn't mention NAME but your example data output does; included NAME

• You might not need to pad the ID or salary so much

• Come of the casts to chars (e.g. on flag columns) can be dropped (if the flag column is 100% guaranteed to always be 1 char long)

• If salary max value in table is larger than an int can hold, consider a cast to something else

• By padding the salary with leading zeroes, the sort will work out. Normalising it to between 0 and 1 could also work, if all the values were padded out to the same width but you possibly then get the problem that loss of precision (dividing a 10 digit salary down to eg 0.123456) will cause two different salaries to merge because there aren't enough digits to fully represent. With any division-that-quantizes-to-lower precision you then risk the original values sorting wrongly (e.g. If salaries of 1000000000 and 1000000001 with id of 2 and 1 respectively both normalise to 0.123456 they would end up sorted wrongly. To guard against this you probably need as many digits for the division answer as the salary had in the first place, padded to a fixed width, but if you've gone that far you might as well just pad all the salaries out either to the width of the widest or to some width that will contain them all. Here utilising a cast to an int might be handy, if the int will overflow. You can make a decision to pad to one digit wider than an int will hold and then if someone inserts a large value in future and your query starts failing because of overflow it at least won't silently deliver wrong results because the pad is chopping digits off the left hand edge. In addressing the cast to bit you can choose whether to add some logic that pads out to the LENGTH() of the string form of the SELECT MAX salary

CONCAT is nice cos you can pass most types to it without first casting to varchar, and it doesn't null the whole thing if you concat a null on, unlike regular string concat ops with + or ||

---

Related Topics