Changing Order of Query Results Example
# result table
display_id$
Example_1 |
Example_14 |
Example_2 |
Example_3 |
# SQL query
ORDER BY CAST(SUBSTRING(display_id$, 9, 2) AS INT) ASC
# Output
display_id$
Example_1 |
Example_2 |
Example_3 |
Example_14 |In this example, you want to order the display IDs by numbers following the phrase ‘Example_’. Example_ consists of 9 characters including the numbered digit at the end. Currently, the values are being ordered by only the first digit per value, but if you’d like to order by digits appended to a word, you can use a SUBSTRING function to extract just the numeric values.
The SUBSTRING function syntax is as follows:
SUBSTRING (expression, position, length)
-
expression: The input source string
-
position: An integer value that specifies the initial position from which the characters can be extracted from the given expression. The first position of an expression is always starting with 1.
-
length: A positive integer value. It specifies the ending limit and determines how many characters are going to be extracted from the given expression. In this case, you are specifying the value as 2 because there can be 2 digits in our alphanumeric display_id$ value.