Databases
StackOverflow2013
Postsdbo
POHow to make Oracle 'order by' behave like SQLServer?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to make Oracle 'order by' behave like SQLServer?
    primarykey
    data
    text
    <p>i'm trying to write an Oracle query that sorts the results in the same way as MS SQL Server does. I'm toying with the <a href="http://download.oracle.com/docs/cd/B28359_01/olap.111/b28126/dml_functions_2038.htm" rel="nofollow">'NLSSORT'</a> function and it's parameters but i can't get exactly the same results as what i can see with MS SQL Server.</p> <p>The context is a generic data collection system that supports both Oracle and MS SQL Server. This is a pretty old system that is still under maintenance and development. No entity framework or any recent approaches to handle database interactions.</p> <p>With a simple order by on MS SQL Server i get this result:</p> <p>_TEST<br> 04-00031-IPE<br> 04-00044-OG<br> 0A-A<br> A0-A<br> SAZ2217 </p> <p>The same query on Oracle returns this:</p> <p>04-00031-IPE<br> 04-00044-OG<br> 0A-A<br> A0-A<br> SAZ2217<br> _TEST </p> <p>I have tried many combinations of NLSSORT parameters without any success.</p> <p>[edit]<br> By using the 'PUNCTUATION' NLS_SORT parameter value, i get results very close to the MS SQL sorting but there is still differences with substrings that contains sequences of numeric chars. Here is a sample query result:</p> <p>Oracle<br> 0031-CASTOR-BLOC1-AV-AP<br> 0031-CASTOR-BLOC1-AV-SP<br> 0031-CASTOR-BLOC1-SV-AP<br> 0031-CASTOR-BLOC1-SV-SP<br> 0031-CASTOR-BLOC10-DV-AP<br> 0031-CASTOR-BLOC10-DV-SP<br> 0031-CASTOR-BLOC2-DV-AP </p> <p>Ms SQL<br> 0031-CASTOR-BLOC10-DV-AP<br> 0031-CASTOR-BLOC10-DV-SP<br> 0031-CASTOR-BLOC1-AV-AP<br> 0031-CASTOR-BLOC1-AV-SP<br> 0031-CASTOR-BLOC1-SV-AP<br> 0031-CASTOR-BLOC1-SV-SP<br> 0031-CASTOR-BLOC2-DV-AP </p> <p>Thank you for your help!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSébastien
    1. USSébastien
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to make Oracle 'order by' behave like SQLServer?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to make Oracle 'order by' behave like SQLServer?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COso what parameters have you specified so far? Looks like it is using ascii. Possible changing to English would help.
      singulars
      1. POHow to make Oracle 'order by' behave like SQLServer?
      1. USColWhi
    2. COEnglish seems to have been disabled by the DBA i guess. If i use: ORDER BY NLSSORT(RESULTATS_DERNIER_MES.UNE_CODE_IDENT, 'NLS_SORT=FRENCH') Oracle handles it (although i get the wrong sorting) but if i use: ORDER BY NLSSORT(RESULTATS_DERNIER_MES.UNE_CODE_IDENT, 'NLS_SORT=ENGLISH') i get an Oracle error ora-12702. I have also tried 'NLS_SORT = BINARY NLS_COMP=LINGUISTIC'
      singulars
      1. POHow to make Oracle 'order by' behave like SQLServer?
      1. USSébastien
    3. CO(sorry for dups) English seems to have been disabled by the DBA i guess. If i use: ORDER BY NLSSORT(RESULTATS_DERNIER_MES.UNE_CODE_IDENT, 'NLS_SORT=FRENCH') Oracle handles it (although i get the wrong sorting) but if i use: ORDER BY NLSSORT(RESULTATS_DERNIER_MES.UNE_CODE_IDENT, 'NLS_SORT=ENGLISH') i get an Oracle error ora-12702. I have also tried 'NLS_SORT = BINARY NLS_COMP=LINGUISTIC' 'NLS_SORT=BINARY NLS_COMP=ANSI' 'NLS_LANGUAGE=AMERICAN' + AI and CI variants.
      singulars
      1. POHow to make Oracle 'order by' behave like SQLServer?
      1. USSébastien
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload