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
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
but in SQL server it giving following result
I want result same like oracle in sql server so how to write query
give the column names !!! in the order you want !!
instead of * give column names
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
i don't understand what you are saying !!!
You will have to order it by the collation Oracle used.
order by prod_name COLLATE Latin1_General_CS_AS;
I don't know what collation will order underscores after "z".
You could use one of the binary collations:
SELECT [name], [description]
WHERE [name] like '%bin%'
ORDER BY Prod_Name COLLATE Latin1_General_BIN;
Personally I would not bother unless it is really needed.
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.
If you could please provide an answer to the following questions
- What version of SQL Server
- What is the collation of the database (right Click and select Properties on DB)
- What is the collation of the column prod_name
Get all your answers from SQL Site
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;
SELECT * FROM @Test1
ORDER BY [Prod_Name] COLLATE SQL_1xCompat_CP850_CI_AS ASC;
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)
Really thanks for valuable information. This information really help me