Oracle sort strings containing section sign §

user_3170181 0
Viewed 64

I'm having trouble getting the right sort order for strings that have the section sign (§) in them.

I've table in Oracle with a column that has the following values Act §10 Act M Act §13(b)(2) Act § 6 Act Rule 10a- 2 Act § 7 Act § 4 Act S

The expected sort should be the following: Act § 4 Act § 6 Act § 7 Act §10 Act §13(b)(2) Act M Act Rule 10a- 2 Act S

The default binary sort puts the strings with § at the end. I used the nlssort function with the paramter nls_sort=generic_m but that gave me the following: Act §10 Act §13(b)(2) Act § 4 Act § 6 Act § 7 Act M Act Rule 10a- 2 Act S

Any help would be appreciated.

sorting oracle
2 Answers

If everything else fails add a virtual column that defines the required transformations and sort on this column.

example

alter table tab
add  col_order as (replace(col,'§ ','§0'));

Note that this is only a sample transformation to address the presented data, you'll have to work hard to get the correct replace for you full data.

The main problem I see, you must fill all parts of the column, that should be interpreted as numbers and are blank, with zeroes.

Now query sorting on the new column col_order

select * from tab order by col_order;
COL             COL_ORDER                                    
--------------- ---------------------------------------------
Act § 4         Act §04                                      
Act § 6         Act §06                                      
Act § 7         Act §07                                      
Act §10         Act §10                                      
Act §13(b)(2)   Act §13(b)(2)                                
Act M           Act M                                        
Act Rule 10a- 2 Act Rule 10a- 2                              
Act S           Act S
user_48081220

You can just remove the character for the purposes of sorting:

WITH testData AS (
    select 'Act §10' as stringVal from dual union all
    select 'Act M' as stringVal from dual union all
    select 'Act §13(b)(2)' as stringVal from dual union all
    select 'Act § 6' as stringVal from dual union all
    select 'Act Rule 10a- 2' as stringVal from dual union all
    select 'Act § 7' as stringVal from dual union all
    select 'Act § 4' as stringVal from dual union all
    select 'Act S' as stringVal from dual
)

SELECT t.stringVal
FROM testData t
ORDER BY REPLACE(t.stringVal, '§', '')
user_88769530
Related