MySQL按一个列排序，该列具有由斜杠分隔的多个数值

Question

I have a table (policy_details) which has column 'notenumber' with values:

150/1  
150/2  
150/1/2  
150/2/1  

I need the resultset shown below using query order by notenumber asc

150/1  
150/1/2  
150/2  
150/2/1  

I have tried:

select *,REPLACE(notenumber, N'/', N'') AS newnotenumber 
from policy_details 
order by newnotenumber asc;

I need to fetch notenumber in ascending order.

Answer 1

Far and away the best solution is to redesign your schema to store each part of notenumber in a different field. Barring this, you need to split out each part of notenumber, convert it to a numeric type, and order by it. You'll have to add a clause to the order by for each part you split out, there's no way around that.

Example:

select
  notenumber,
  replace(substring(substring_index(notenumber, '/', 1), length(substring_index(notenumber, '/', 1 - 1)) + 1), '/', ''),
  replace(substring(substring_index(notenumber, '/', 2), length(substring_index(notenumber, '/', 2 - 1)) + 1), '/', ''),
  replace(substring(substring_index(notenumber, '/', 3), length(substring_index(notenumber, '/', 3 - 1)) + 1), '/', '')
from test
order by
  convert(replace(substring(substring_index(notenumber, '/', 1), length(substring_index(notenumber, '/', 1 - 1)) + 1), '/', ''), signed integer),
  convert(replace(substring(substring_index(notenumber, '/', 2), length(substring_index(notenumber, '/', 2 - 1)) + 1), '/', ''), signed integer),
  convert(replace(substring(substring_index(notenumber, '/', 3), length(substring_index(notenumber, '/', 3 - 1)) + 1), '/', ''), signed integer)
;

Note you'll need to insert the index number of the split part twice into each replace clause.

SQL fiddle for the above solution: http://sqlfiddle.com/#!9/dc935/1/0