How to make Oracle 'order by' behave like SQLServer?

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 'NLSSORT' function and it's parameters but i can't get exactly the sa开发者_Python百科me results as what i can see with MS SQL Server.

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.

With a simple order by on MS SQL Server i get this result:

_TEST

04-00031-IPE

04-00044-OG

0A-A

A0-A

SAZ2217

The same query on Oracle returns this:

04-00031-IPE

04-00044-OG

0A-A

A0-A

SAZ2217

_TEST

I have tried many combinations of NLSSORT parameters without any success.

[edit]

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:

Oracle

0031-CASTOR-BLOC1-AV-AP

0031-CASTOR-BLOC1-AV-SP

0031-CASTOR-BLOC1-SV-AP

0031-CASTOR-BLOC1-SV-SP

0031-CASTOR-BLOC10-DV-AP

0031-CASTOR-BLOC10-DV-SP

0031-CASTOR-BLOC2-DV-AP

Ms SQL

0031-CASTOR-BLOC10-DV-AP

0031-CASTOR-BLOC10-DV-SP

0031-CASTOR-BLOC1-AV-AP

0031-CASTOR-BLOC1-AV-SP

0031-CASTOR-BLOC1-SV-AP

0031-CASTOR-BLOC1-SV-SP

0031-CASTOR-BLOC2-DV-AP

Thank you for your help!

---

I finally found this solution:

ORDER BY NLSSORT(COLUMN_NAME, 'NLS_SORT = FRENCH_M')  

At least in my particular context, i get the same sorting under both MS SQL Server (default sorting) and Oracle.

Here is two useful links:

http://www.myoracleguide.com/xl/Linguistic_Sorting_Frequently_Asked_Questions.htm

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch5lingsort.htm#NLSPG005

---

Could consider use of rpad function?

e.g.

select name, rpad(upper(replace(translate(name,'_','+'),'-','') ),15,'0') as v1
from sorttest order by 
rpad(upper(replace(translate(name,'_','+'),'-','') ),15,'0')