1

I have a varchar column [flat_number] which has some values as

A1A1A
A1A2A
A1A101A
A3A5A
12
A2
A3

I wish to get the results sorted by number and then by characters first

like

12
A2
A3
A1A1A
A1A2A
A1A101A
A3A5A

I have managed to sort it by number (if the column only contains digits 0-9 using regex, I treat it as a number), then I sort it by character, but it dosent seem to work for column values as A1A101A (having multiple combinations of number and character)

CASE
  WHEN length(flat_number) < 10 AND flat_number SIMILAR TO '[0-9]+'
THEN
  flat_number::int
END
ELSE
  NULL
END,
( SELECT COALESCE( match[1], NULL ) FROM regexp_matches( flat_number, '[^0-9]+' ) AS match ),
( SELECT COALESCE( left( match[1], 9), NULL ) FROM regexp_matches( flat_number, '([0-9]+$)' ) AS match )::int

The current query works as

If the column contains only numbers [0-9] I convert it to int and sort it ElSE, I split the column into 2 parts and try to extract the column character at the start and the number at the end.

Is there a better wait to do so and also make sure the expected output is returned

mu is too short
  • 426,620
  • 70
  • 833
  • 800
Akash
  • 4,956
  • 11
  • 42
  • 70

1 Answers1

0

I think the following will work:

order by (case when flat_number ~ '^[0-9]+$' then 0 else 1 end),
         (case when flat_number ~ '^[0-9]+$' then length(flat_number) end),
         flat_number

This orders the number first, uses a trick to put them in numeric order, and then orders the rest of the numbers.

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • dosent seem to work, I have created a fiddle for it: http://sqlfiddle.com/#!12/10aeb/2 – Akash Feb 16 '13 at 03:37
  • Also, there's no fixed sequence of chars and numbers, it can be CNCN, NCNCN, NNNCC (N Number, C Char) – Akash Feb 16 '13 at 03:42