SQL Server Query of Order by giving different result after migration from oracle to sql

Transact-SQL
1

I want result like oracle query so what is the option in sql server 2016

Details given below

in oracle for e.g.

create table test1
(
Prod_Name varchar2(30)
)

insert into test1 values('CHANNEL')

insert into test1 values('SHELL')

insert into test1 values('_DISTRIBUTOR BELT')

select * from test1 order by prod_name asc

Select Query giving following result

Prod_Name

CHANNEL
SHELL
_DISTRIBUTOR BELT

but in SQL server it giving following result

Prod_Name

_DISTRIBUTOR BELT
CHANNEL
SHELL

I want result same like oracle in sql server so how to write query

1 Like
2

give the column names !!! in the order you want !!

instead of * give column names

select
*
from
test1
order by
prod_name asc

select
Prod_Name,CHANNEL,SHELL,_DISTRIBUTOR BELT
from
test1
order by
prod_name asc

3

Here prod_name is column name of table and 3 row in table like 1)SHELL,2) CHANNEL,3) ,_DISTRIBUTOR BELT

but in sql server 3) rows showing first when i use order by of column prod_name but i want that in row last like oracle query result

4

i don't understand what you are saying !!!

sorry !!!

5

You will have to order it by the collation Oracle used.

select *
from test1
order by prod_name  COLLATE Latin1_General_CS_AS;

I don't know what collation will order underscores after "z".

6

You could use one of the binary collations:

SELECT [name], [description]
FROM sys.fn_helpcollations()
WHERE [name] like '%bin%'

eg

SELECT Prod_Name
FROM test1
ORDER BY Prod_Name COLLATE Latin1_General_BIN;

Personally I would not bother unless it is really needed.

7

Sorting strings is governed/controlled by collations. Collations are not the same between various RDBMSs, but we should at least be able to get close. What collation is being used in Oracle? Once we know that, we can find the closest matching option in SQL Server.

8

If you could please provide an answer to the following questions

  1. What version of SQL Server
  2. What is the collation of the database (right Click and select Properties on DB)
  3. What is the collation of the column prod_name
9

Thanks

10

Get all your answers from SQL Site

11

Tushar: please be aware that binary collations do not sort upper-case and lower-case versions of the same letters together. Your sample data is very simple and only contains upper-case letters so it might mask problems with binary collations if your data is not entirely upper-case.

It would greatly help to know what encoding you are using for VARCHAR in Oracle. Is it a Unicode encoding, such as UTF-8 or UTF-16? Or is it an 8-bit code page, such as Windows-1252? That will make a difference here. If the encoding needs to be Unicode (i.e. NVARCHAR = UTF-16 in SQL Server is only Unicode option until SQL Server 2019 when VARCHAR can be UTF-8), then you might need a binary collation. But if you are using an 8-bit code page, then there might be a better solution that will at least keep upper-case and lower-case of the same letter together.

Here is an example showing both the problem with the binary collation, and the only 8-bit collation that sorts an underscore after the letters:

DECLARE @Test1 TABLE ([Prod_Name] VARCHAR(30));

INSERT INTO @Test1 VALUES
('apple'), ('shell'), ('CHANNEL'), ('SHELL'), ('_DISTRIBUTOR BELT');

SELECT * FROM @Test1
ORDER BY [Prod_Name] COLLATE Latin1_General_100_BIN2 ASC;
/*
CHANNEL
SHELL
_DISTRIBUTOR BELT
apple
shell
*/

SELECT * FROM @Test1
ORDER BY [Prod_Name] COLLATE SQL_1xCompat_CP850_CI_AS ASC;
/*
apple
CHANNEL
SHELL
shell
_DISTRIBUTOR BELT
*/

Of course, even if you are using an 8-bit encoding, SQL_1xCompat_CP850_CI_AS is not necessarily the best option IF you have Extended ASCII characters (values in the 128 - 255 range), and are not currently using Windows-850, and you have characters that are not common between your current code page and Windows-850.

If you do need to use a binary collation, then please use Latin1_General_100_BIN2 instead of Latin1_General_BIN. It is newer and sorts Unicode Supplementary Characters properly (whereas the BIN collations do not). Basically, anyone using SQL Server 2008 or newer should be using the _BIN2 collations and not the _BIN collations. For more details on why, please see my post: Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2)

12

Really thanks for valuable information. This information really help me