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.
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