1

I have a list of records in PostgreSQL as follows, these are actually sections of various books The records are generated in the below format.

1            
7.1        
6.2   
7.1    
7.4   
6.8.3   
6.8.2     
10     
1.1     
7.6     
6.1     
11    
8.3     
8.5     
1.1.2      
6.4      
6.6      
8.4      
1.1.6       
6.8.1        
7.7.1          
7.5          
7.3

I want to sort it like this 

 1         
 1.1          
 1.1.2          
 1.1.6             
 6.2              
 6.4    
 6.5    
 6.6    
 6.7    
 6.8.1    
 6.8.2    
 6.8.3    
 7.2    
 7.3    
 7.4    
 7.5    
 7.6    
 7.7.1    
 7.7.2    
 8.3    
 8.4    
 8.5
 10

It's a varchar column so i have tried using what listed here. Sorting records from Oracle with multiple decimal points (.)

select * from tbl_wo_kitting where wo_project_id = 1000033
ORDER BY to_number(regexp_substr(line_no, '[^.]+', 1, 1)) NULLS FIRST

It keeps saying invalid function name regexp_substr. What are the functions to sort that way?

Thank you very much for your help.

Improve this question
1
  • 1
    Postgresql. Sorry selected the wrong flag. Edited. Commented Jul 14, 2016 at 7:24

1 Answer 1

Reset to default
2

You can do this by converting the string to an integer array, then sort on the array:

select * 
from tbl_wo_kitting 
where wo_project_id = 1000033
ORDER BY (string_to_array(line_no, '.'))::int[]

Note that this will fail if there are values that cannot be converted to numbers in the line_no_ column.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much. that actually work. I have to convert the varchar value first then sort it using ORDER BY (string_to_array(line_no, '.'))::int[]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.