Alpha numeric Sorting Issue with BIP-Siebel report - XML Publisher

Question

The report contains a column called 'Site Name' which contains alpha-number values such as '111 street', '4th Cross', 'Road cross', 'North end street' etc. The sorting expected here is in the form:

111 street 412 Cross North end street Road cross

I am currently using the following syntax for sorting the same -

<?sort:(xdoxslt:convert_case(SiteName,'UPPER'));'ascending';data-type='text'?>

However, the output which I am getting is not as per expected.

North end street Road cross 111 street 412 Cross

Can anyone help me on this.

Thanks, Arun

Answer 1

If this problem occurs with every query it may be because of your character set and language settings.

Numbers are sorted before letters for every NLS_SORT, except possibly for BINARY (the default). If you're using BINARY, the order depends on the character set. Some character sets, such as EBCDIC, have letters before numbers.

These queries will show the relevant settings:

select sys_context('userenv', 'nls_sort') from dual;
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';