1

How to Sort Character column numerically. I have a column of numbers stored as chars. When I do a ORDER BY for this column I get the following:

100D
131A
200
21B
30
31000A
etc.

There may be chance of having one Alphabet at the end. How can I order these chars numerically? Do I need to convert something or is there already an SQL command or function for this?

srini
  • 6,719
  • 8
  • 32
  • 36

1 Answers1

3

You could use something like:

ORDER BY Cast(regexp_replace(yourcolumn, '[^0-9]', '', 'g') as integer)
DavidEG
  • 5,857
  • 3
  • 29
  • 44
  • HI David can u explain what happens when i write regex_replace( , , , ) – srini Dec 21 '11 at 14:14
  • In this case is replacing any non number by an empty string. Function definition: http://www.postgresql.org/docs/9.1/static/functions-string.html. POSIX Regular Expressions: http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP – DavidEG Dec 21 '11 at 14:37