2

I have the following sample data for a particular column symbol for sample table.

(Update:) The data is not in a regular pattern. Number may occur at any place in between characters. 

symbol

COL4A1
COL4A3
COL8A2
COL2A1
COL12A1
COL12A1
COL16A1
COL19A1

I need to sort the this data on database level. I used the following query:

select symbol from sample order by symbol asc

Result is follows:

COL12A1
COL12A1
COL16A1
COL19A1
COL2A1
COL4A1
COL4A3
COL8A2

But I need to get the order in the following way:

COL2A1
COL4A1
COL4A3
COL8A2
COL12A1
COL12A1
COL16A1
COL19A1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Sivaram Chintalapudi
  • 456
  • 7
  • 25
  • I see that your edit changed your question's meaning. If you drastically change the question's parameters consider asking a new question instead. Also, no notifications get sent when you edit a question, so nobody knew you'd changed it into a different question. – Craig Ringer Oct 18 '12 at 23:23
  • 2
    I've posted a follow-up to your question that describes the new problem, with suitably variable sample data, a SQLFiddle, etc. Please have a look and see if it accurately describes your problem: http://stackoverflow.com/questions/12965463/humanized-or-natural-number-sorting-of-mixed-word-and-number-strings – Craig Ringer Oct 18 '12 at 23:54
  • @Craig Ringer, I got the solution from one of my team mate. It fixes my issues. Please see the below answer for this. – Sivaram Chintalapudi Oct 19 '12 at 08:55

2 Answers2

4

PostgreSQL doesn't offer a number-aware collation that can do "humanized" sorts like "1A, 2A, 3A, ... 10A, 11A, ...". It relies on the operating system for collation, and I'm not aware of any OS that exposes such a collation to applications.

To do this, you need to split the text according to a pattern and order by the pattern parts, probably using regexp_matches.

CREATE TABLE Table1 ("symbol" text);
INSERT INTO Table1 ("symbol") VALUES
    ('COL4A1'),('COL4A3'),('COL8A2'),('COL2A1'),
    ('COL12A1'),('COL12A1'),('COL16A1'),('COL19A1');

WITH matched(symbol, symbol_parts) AS (
  SELECT symbol, regexp_matches(symbol, '(\D*)(\d+)(\D+)(\d+)')
  FROM Table1
)
SELECT symbol 
FROM matched
ORDER BY symbol_parts[1], symbol_parts[2]::integer,
         symbol_parts[3], symbol_parts[4]::integer;
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 3
    And if you're doing this a lot then precomputing and storing the parts (or converting them to something sortable such as `COL002A001` on INSERT/UPDATE) might be a good idea. – mu is too short Oct 16 '12 at 16:32
  • @SivaramChintalapudi Just FYI I've corrected the regular expression to cope with codes that have a number first and to more correctly match non-digits. – Craig Ringer Oct 20 '12 at 09:14
2
CREATE OR REPLACE FUNCTION pad_numbers(text)
              RETURNS text AS
            $BODY$
                SELECT regexp_replace(
                regexp_replace(
                  regexp_replace(
                    regexp_replace(
                      $1, 
                      E'(^|\\D)(\\d{1,3}($|\\D))', E'\\1000\\2', 'g'
                    ), E'(^|\\D)(\\d{4,6}($|\\D))', E'\\1000\\2', 'g'
                  ), E'(^|\\D)(\\d{7}($|\\D))', E'\\100\\2', 'g'
                ), E'(^|\\D)(\\d{8}($|\\D))', E'\\10\\2', 'g'
              );
            $BODY$
              LANGUAGE 'sql' VOLATILE;


select symbol from sample order by pad_numbers(symbol) asc
Sivaram Chintalapudi
  • 456
  • 7
  • 25