Home Forums SQL Server 2008 SQL Server 2008 - General SQL Server Collation (ASCII Table sort and Case Insensitive/Accent Insensitive) RE: SQL Server Collation (ASCII Table sort and Case Insensitive/Accent Insensitive)

  • Hi 🙂

    Thank you Lowell and SQL Kiwi for your answers.

    Our application send SQL requests one-by-one via ODBC cursor and then fetch next. With the good collation, SQL would return the good one first result and then the fetch next would return the next ones in order.

    I've tried SQL Kiwi solution and it doesn't work :hehe:

    USE [TestCollation

    GO

    /****** Object: Table [dbo].[tmp] Script Date: 01/24/2012 09:48:06 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[tmp](

    [char_name] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,

    [ascii_val] [smallint] NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    INSERT INTO [TestCollationFrench].[dbo].[tmp]

    ([char_name]

    ,[ascii_val])

    VALUES

    (<char_name, varchar(1),>

    ,<ascii_val, smallint,>)

    First print screen: initial data

    Second print screen :SELECT * FROM dbo.tmp ORDER BY char_name. You'll notice that the results are not ordered by the column ascii_val order. Only the 16 firt lines are correctly ordered.

    Third print screen: This is what i want to obtain. Results expected (almost ordered by ascii_val column with numbers before letters).

    Have a nice day 🙂