PL SQL + Sorting the text data

Question

Hi There,

I have a column in which the data is in string format shown in below image ( Column A). The out put should be sorted from lower to bottom with space in between. ( i.e 102 MB)

I am using Netezza as database and PLSQL.

I have the query Select ColumnA from tableA; either I have to derive another column or any variable to acheive the output shown in image

Answer 1

Check if this script works on your server:

declare @table as table (A varchar(10))  
  
insert @table values  
('5GB'),  
('50GB'),  
('500MB'),  
('1GB'),  
('14GB'),  
('11GB'),  
('10GB'),  
('102MB')  
  
select * from @table  
  
---  
  
;  
with Q as  
(  
    select *, case when A like '%GB' then 1000 else 1 end as k, translate(A, 'MGB', '   ') as n  
    from @table  
)  
select replace(replace(A, 'GB', ' GB'), 'MB', ' MB') as A  
from Q  
order by n * k  

or this:

select replace(replace(A, 'GB', ' GB'), 'MB', ' MB') as A  
from @table  
order by cast(replace(replace(A, 'MB', ''), 'GB', '000') as int)