Patrick

My feedback

  1. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    4 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Patrick commented  · 

    Thanks for your suggestion by using the new collation (version 100) explicitly (instead of default version 90) as described in
    https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017

    However, I have enumerated all the characters (except combining high/low-surrogate characters) that should be treated as standalone character

    DECLARE @idx INT = 1;
    DECLARE @result INT;
    DECLARE @emptychar TABLE
    (
    codepoint nvarchar(5),
    character nvarchar(1)
    )

    WHILE @idx <= 0xffff
    BEGIN
    SET @result = 0
    SELECT @result = 1 where nchar(@idx)='' COLLATE Latin1_General_100_CI_AS
    IF @result = 1
    Insert into @emptychar(codepoint,character) values (CONVERT(VARCHAR(10), CONVERT(VARBINARY(2), @idx), 2), NCHAR(@idx))
    SET @idx = @idx + 1;
    END;

    select * from @emptychar

    The range (U+A500 - U+A62B), (U+0514 - U+052F) and (U+0D70 - U+0D7F) characters should also be handled though I may not use it.
    http://www.unicode.org/charts/PDF/UA500.pdf
    http://www.unicode.org/charts/PDF/U0500.pdf
    http://www.unicode.org/charts/PDF/U0D00.pdf

    An error occurred while saving the comment
    Patrick commented  · 

    More supplementary example is provided.

    DECLARE @testchar TABLE
    (
    idx int,
    val nvarchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS
    )

    insert into @testchar (idx,val) values (1,N'a㘭b')
    insert into @testchar (idx,val) values (2,N'ab')
    insert into @testchar (idx,val) values (3,N'C')
    insert into @testchar (idx,val) values (4,N'c')

    select * from @testchar where val like N'%㘭%' -- Result: 1,2,3,4; Correct: 1
    select * from @testchar where val like N'%a㘭b%' -- Result: 1,2; Correct 1
    select * from @testchar where val like N'%c%' -- Result: 3,4; Correct 3,4

    Patrick shared this idea  · 

Feedback and Knowledge Base