I want to sort the search results by alphanumeric column such that it shows rows with alphabets first and the at last it should show rows with numeric characters.
Current result
12
13
14
aa
ab
bb
bd
Expected Result
aa
ab
bb
bd
11
12
13
5
-
Are there any mixed values possible, e.g. 'a1', '1a'? If yes, how to treat them?Thorsten Kettner– Thorsten Kettner2017-02-06 09:16:56 +00:00Commented Feb 6, 2017 at 9:16
-
yes there are mix values if there are two values like a1 & 1a then it should show in following order a1, 1aSandip Tajne– Sandip Tajne2017-02-06 09:18:43 +00:00Commented Feb 6, 2017 at 9:18
-
But where are they placed among the fully alphabetic or numerich values? Please show the complete order for 'aa', 'ab', 'a1', 'a2', 'b1', '1a', '1b', '11', '12'. Are all values exactly two characters long?Thorsten Kettner– Thorsten Kettner2017-02-06 09:21:48 +00:00Commented Feb 6, 2017 at 9:21
-
And what about capital letters? Can they occur? Shall 'A' and 'B' come before 'a' and 'b' then or after or doesn't it matter? Please edit your request to give us a full picture of available combinations and how you want them sorted.Thorsten Kettner– Thorsten Kettner2017-02-06 09:27:33 +00:00Commented Feb 6, 2017 at 9:27
-
order of a & A doesn't matter it should come in following order : aa, ab, a1, a2, b1, 1a, 1b, 11, 12Sandip Tajne– Sandip Tajne2017-02-06 09:53:09 +00:00Commented Feb 6, 2017 at 9:53
Add a comment
|
4 Answers
You can check if a string is case sensitive:
with a_table(col) as (
values
('12'), ('13'), ('14'), ('aa'), ('ab'), ('bb'), ('bd')
)
select *
from a_table
order by lower(col) = upper(col), col;
col
-----
aa
ab
bb
bd
12
13
14
(7 rows)
Comments
SELECT ... FROM table order by CASE WHEN column < 'A' THEN lpad(column, size, '0') ELSE column END;
Hope this will work.
2 Comments
Sandip Tajne
please let me know what is size in lpad(column, size, '0')
Shubham Srivastava
The size var is the length of the varchar column, e.g 255 for VARCHAR(255)
... ORDER BY (mycol SIMILAR TO '[0-9]*'), mycol
Comments
You want to apply a certain sort order on characters with
- a = A
- a < b
- z < 0
You can achieve this by repacing characters in your string, e.g. 'a' -> '01', b -> '02', '0' -> '90', '1' -> '91', 'abc123' -> '010203919293'. You would write a function for this, you would then call from your query.
select col
from mytable
order by get_sortkey(col);
A simple alternative may be to use the TRANSLATE function. The disadvantage: it replaces each character only with another single character, not with two or more as in above example. Knowing the ASCII codes we can use:
select col
from mytable
order by translate(col,
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
'0123456789ABCDEFGHIJKLMNOP0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
) collate "C";
