I am having a sorting issue with oracle 10g. Not sure if it is specific to 10g or not.
I have the following table:
ID NAME
1 A.1
2 A.3
3 A.4
4 A.5
5 A.2
6 A.5.1
7 A.5.2
8 A.5.10
9 A.5.10.1
10 A.5.3
Performing the generic SELECT NAME FROM table_name ORDER BY 1
produces:
A.1
A.2
A.3
A.4
A.5
A.5.1
A.5.10
A.5.10.1
A.5.2
A.5.3
I would like it to sort correctly when those sections have numbers greater than 9, like so:
A.1
A.2
A.3
A.4
A.5
A.5.1
A.5.2
A.5.3
A.5.10
A.5.10.1
I have way more number entries than this with varying lengths and many sections with number segments greater than 10. I was trying to mess around with regexp_replace() in the order by clause but have had no luck. Any help would be greatly appreciated.
'LPAD(lower(column_name))' is used to sort the varchar field numerically in MySQL. Let us see an example. Firstly, we will create a table. The CREATE command is used to create a table.
Introduction to Oracle ORDER BY clause To sort data, you add the ORDER BY clause to the SELECT statement as follows: SELECT column_1, column_2, column_3, ... FROM table_name ORDER BY column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST], column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST], ...
Similarly, I store phone numbers in a VARCHAR2 datatype.
Try this
WITH t AS
(
SELECT id,name,
xmltype('<r><c>' ||replace(NAME, '.', '</c><c>')||'</c></r>') AS xmlname
FROM table1
)
SELECT name ,id
FROM t
ORDER BY lpad(extract(xmlname,'//c[1]/text()').getstringval(), 5, '0')
||lpad(extract(xmlname,'//c[2]/text()').getstringval(), 5, '0')
||lpad(extract(xmlname,'//c[3]/text()').getstringval(), 5, '0')
||lpad(extract(xmlname,'//c[4]/text()').getstringval(), 5, '0')
Here is a fiddle
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With