CODEWITHSUNDEEP
sqlpostgresql
Mihir Garg
Mihir Garg

Reputation: 29

Sorting Text Value in SQL

I have a table with a text field. The field stores data as <60%,>90%, 60%-70%, 80%-90%. I want to sort the data so that the result should look like : <60%, 60%-70%, 70%-80%, 80%-90%, >90%. How can I achieve this in postgresql?

Upvotes: 2

Views: 51

Answers (2)

user330315
user330315

Reputation:

I would convert the string to an integer range 

select *,
       case 
         when col_x like '>%' then '('||regexp_replace(col_x, '[>%]', '', 'g')||',)'
         when col_x like '<%' then '(,'||regexp_replace(col_x, '[<%]', '', 'g')||')'
         else '['||translate(col_x, '-%', ',')||')'
       end::int4range as range
from x
order by range;

The CASE expressions converts <60% to the range (,60) and >90% to the range (90,)

Online example: https://rextester.com/KIK90562

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

use regex_replace()

select * from tableA order by regexp_replace(sortdata, '\D','','')

see dbfiddle

Upvotes: 1

Related Questions