none
collation sorting RRS feed

  • Question

  • are there any difference between the SQL_Latin_gerneral_CP1_CI_AI and SQL_Latin_gerneral_CP437_CI_AI

    sorting ? how to make a example to show ?

    Wednesday, August 1, 2018 3:03 AM

All replies

  • I used to insert the following records

    insert into testing(col2) values ('®')

    where col2 is an varchar(20) col

    select cast(col2 as varbinary(max)),col2 from testing

    0xAE ®          <<< Give this results when collation is SQL_Latin_gerneral_CP1_CI_AI
    0x72 r            <<< Give this results when collation is SQL_Latin_gerneral_CP437_CI_AI

    Wednesday, August 1, 2018 3:15 AM

  • ® Corresponds to a hex value of AE

    This you can check using the below illustration

    And hex 72 corresponds to r

    And regarding your issue you can solve it by storing it within unicode based datatype column

    see illustration below

    declare @t1 table(
    n1 nvarchar(20) collate SQL_Latin1_General_CP1_CI_AI ,
    n2 nvarchar(20) collate SQL_Latin1_General_CP437_CI_AI
    )
    
    insert @t1
    values (N'®',N'®')
    
    select *
    from @t1

    result here


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Wednesday, August 1, 2018 5:53 AM
    Wednesday, August 1, 2018 5:24 AM
  • You can get the Information about the differences directly from SQL Server:

    select *
    from sys.fn_helpcollations() AS COL
    where col.name in ('SQL_Latin1_General_CP1_CI_AI', 'SQL_Latin1_General_CP437_CI_AI')


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 1, 2018 6:06 AM
  • but how ® can be interpret to 'r' in SQL_Latin_gerneral_CP437_CI_AI??

    Wednesday, August 1, 2018 8:19 AM
  • here is an intereting results : under 'SQL_Latin1_General_CP437_CI_AI'

    Wednesday, August 1, 2018 8:21 AM
  • but how ® can be interpret to 'r' in SQL_Latin_gerneral_CP437_CI_AI??

    Where is it interpreted as r?

    You're not using the proper datatype

    Store it in unicode based column as suggested and it will work fine regardless of your collation


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, August 1, 2018 8:30 AM
  • here is an intereting results : under 'SQL_Latin1_General_CP437_CI_AI'


    Its giving the expected result only i.e character corresponding to the value passed

    As specified earlier your issue is not capturing the unicode based info

    Use nvarchar as the datatype then it will store correct value and display it


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, August 1, 2018 8:36 AM